﻿from fastapi import FastAPI, Request, Form, HTTPException, Depends
from fastapi.responses import HTMLResponse, RedirectResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from fastapi.middleware.cors import CORSMiddleware  # 添加这行导入
import uvicorn
from pydantic import BaseModel
from pymysql import cursors
from typing import List, Optional
import pymysql
from mysql import DB_CONFIG  # 从你的mysql.py导入配置
import asyncio  # 新增导入
import hashlib, hmac, json, os, sys, time, requests
from datetime import date, datetime, timedelta 
from fastapi import UploadFile, File
import random
import os
import uuid
import binascii

# 新增邮件相关库
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import logging
# 修改后

# 接口加密
from encryption_module import require_encryption, get_decrypted_data, encryption_manager

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger("main")

# 加载支付宝工具
import alipay_utils
import pymysql.cursors as cursors
from pymysql.converters import escape_string
import random
import requests
import traceback

# Import from alipay_utils using the new functions
# from alipay_utils import get_official_alipay_client_and_config, verify_alipay_notification_signature

# Official Alipay SDK model and request for App Pay
from alipay.aop.api.request.AlipayTradeAppPayRequest import AlipayTradeAppPayRequest
from alipay.aop.api.domain.AlipayTradeAppPayModel import AlipayTradeAppPayModel

app = FastAPI()

# 添加CORS中间件
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # 开发环境可以使用*，生产环境建议指定具体域名
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

class UserCreate(BaseModel):
    username: str
    password: str
    hobby: Optional[str] = None

# 获取数据库连接
def get_conn():
    return pymysql.connect(**DB_CONFIG)



# 互亿无线API配置
IHUYI_CONFIG = {
    'account': 'IC61352040',
    'password': '04b02bc8de6608398e1b6eb20e96ab68',
    'app': 'id2',
    'url': 'https://api.ihuyi.com/idcard/Submit.json'
}



# 添加身份证号隐私保护函数
def mask_id_card(id_card_no):
    """
    身份证号隐私保护，中间部分用*替代
    保留前4位和后4位，中间用*替代
    """
    if not id_card_no or len(id_card_no) < 8:
        return id_card_no
    
    if len(id_card_no) == 18:
        # 18位身份证：保留前4位和后4位
        return id_card_no[:4] + '*' * 10 + id_card_no[-4:]
    elif len(id_card_no) == 15:
        # 15位身份证：保留前4位和后3位  
        return id_card_no[:4] + '*' * 8 + id_card_no[-3:]
    else:
        # 其他长度：保留前后各2位
        return id_card_no[:2] + '*' * (len(id_card_no) - 4) + id_card_no[-2:]

# 修改实名认证路由中的存储部分
@app.post("/users/real-name-auth/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def real_name_authentication(request: Request):
    """
    实名认证接口
    接收参数：userid, name, id_card_no
    """
    try:
        data = get_decrypted_data(request)  # 获取解密后的数据
        userid = data.get("userid")
        name = data.get("name")
        id_card_no = data.get("id_card_no")
        
        # 参数验证
        if not all([userid, name, id_card_no]):
            return {"status": "error", "message": "缺少必要参数：userid, name, id_card_no"}
        
        # 检查用户是否存在
        conn = get_conn()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT userid, extraData FROM users WHERE userid = %s", (userid,))
                user = cursor.fetchone()
                if not user:
                    return {"status": "error", "message": "用户不存在"}
                
                # 构建实名认证请求参数
                _now = str(int(time.time()))
                password_raw = (IHUYI_CONFIG['account'] + 
                              IHUYI_CONFIG['password'] + 
                              IHUYI_CONFIG['app'] + 
                              name + 
                              id_card_no + 
                              _now)
                password_md5 = hashlib.md5(password_raw.encode('UTF-8')).hexdigest()
                
                # 准备API请求数据
                api_data = {
                    'account': IHUYI_CONFIG['account'],
                    'password': password_md5,
                    'app': IHUYI_CONFIG['app'],
                    'name': name,
                    'id_card_no': id_card_no,
                    'time': _now,
                }
                
                # 请求头
                headers = {
                    "Content-type": "application/x-www-form-urlencoded",
                    "Accept": "text/plain"
                }
                
                # 发起实名认证请求
                logger.info(f"开始为用户 {userid} 进行实名认证，姓名：{name}")
                response = requests.post(IHUYI_CONFIG['url'], headers=headers, data=api_data, timeout=30)
                
                # 解析响应
                try:
                    auth_result = response.json()
                except json.JSONDecodeError:
                    logger.error(f"实名认证API返回非JSON格式: {response.text}")
                    return {"status": "error", "message": "实名认证服务响应格式错误"}
                
                logger.info(f"实名认证API响应: {auth_result}")
                
                # 处理现有的extraData
                current_extra_data = user[1] if user[1] else {}
                if isinstance(current_extra_data, str):
                    try:
                        current_extra_data = json.loads(current_extra_data)
                    except json.JSONDecodeError:
                        current_extra_data = {}
                
                # 只存储关键的实名认证信息到extraData的realNameAuth键下
                auth_status = auth_result.get('result', {}).get('status', 0)
                current_extra_data['realNameAuth'] = {
                    "verified": auth_status == 2,  # 是否认证通过
                    "status": auth_status,  # 认证状态：0未认证，1未通过，2通过
                    "name": name,  # 认证姓名
                    "idCardNo": mask_id_card(id_card_no),  # 隐私保护后的身份证号
                    "authTime": datetime.now().isoformat(),  # 认证时间
                    "flowId": auth_result.get('idcardid', '0'),  # 流水号
                    "message": auth_result.get('msg', '')  # API返回信息
                }
                
                # 更新数据库
                update_sql = "UPDATE users SET extraData = %s WHERE userid = %s"
                cursor.execute(update_sql, (json.dumps(current_extra_data, ensure_ascii=False), userid))
                conn.commit()
                
                # 返回结果
                status_msg = {
                    0: "未认证",
                    1: "认证未通过", 
                    2: "认证通过"
                }.get(auth_status, "未知状态")
                
                return {
                    "status": "success",
                    "message": status_msg,
                    "data": {
                        "userid": userid,
                        "authStatus": auth_status,
                        "statusMsg": status_msg,
                        "verified": auth_status == 2,
                        "flowId": auth_result.get('idcardid', '0'),
                        "authTime": current_extra_data['realNameAuth']['authTime'],
                        "idCardNo": mask_id_card(id_card_no)  # 返回时也使用隐私保护
                    }
                }
                
        except requests.exceptions.RequestException as e:
            logger.error(f"实名认证API请求失败: {str(e)}")
            return {"status": "error", "message": f"实名认证服务请求失败: {str(e)}"}
        except Exception as e:
            logger.error(f"数据库操作失败: {str(e)}")
            return {"status": "error", "message": f"数据库操作失败: {str(e)}"}
        finally:
            conn.close()
            
    except Exception as e:
        logger.error(f"实名认证接口异常: {str(e)}")
        return {"status": "error", "message": f"服务器内部错误: {str(e)}"}





# 版本信息路由
@app.get("/app/version-info/")
async def get_version_info():
    """
    获取应用版本信息
    返回版本号、最新版本日期、更新说明等信息
    """
    try:
        # 版本信息配置
        version_info = {
            "version": "1.0.0",
            "versionCode": 1,
            "releaseDate": "2025-06-15",
            "updateTime": "2025-06-15 10:30:00",
            "updateNotes": [
                "新增实名认证功能",
                "优化用户体验",
                "修复已知问题",
                "提升系统稳定性"
            ],
            "features": [
                "支付宝支付集成",
                "实名认证系统",
                "用户管理功能",
                "数据安全保护"
            ],
            "minSupportedVersion": "1.0.0",
            "forceUpdate": False,
            "downloadUrl": "https://example.com/download/app-v1.2.3.apk",
            "fileSize": "65.6MB",
            "changelog": {
                "added": ["新增实名认证功能", "新增版本信息接口"],
                "improved": ["优化数据库查询性能", "提升用户界面响应速度"],
                "fixed": ["修复登录状态异常问题", "修复数据同步错误"]
            },
            "systemRequirements": {
                "android": "5.0+",
                "ios": "10.0+",
                "ram": "2GB+",
                "storage": "100MB+"
            },
            "serverInfo": {
                "apiVersion": "v1",
                "serverTime": datetime.now().isoformat(),
                "timezone": "Asia/Shanghai",
                "maintenance": False
            }
        }
        
        return {
            "status": "success",
            "message": "获取版本信息成功",
            "data": version_info
        }
        
    except Exception as e:
        logger.error(f"获取版本信息失败: {str(e)}")
        return {
            "status": "error", 
            "message": f"获取版本信息失败: {str(e)}"
        }






# 同时更新查询状态的路由，返回时也显示隐私保护后的身份证号
@app.get("/users/{userid}/real-name-status/")
async def get_real_name_status(userid: int):
    """
    查询用户实名认证状态
    """
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT username, extraData FROM users WHERE userid = %s", (userid,))
            user = cursor.fetchone()
            
            if not user:
                return {"status": "error", "message": "用户不存在"}
            
            username, extra_data = user
            
            # 解析extraData
            if not extra_data:
                return {
                    "status": "success",
                    "data": {
                        "userid": userid,
                        "username": username,
                        "verified": False,
                        "message": "未进行实名认证"
                    }
                }
            
            if isinstance(extra_data, str):
                try:
                    extra_data = json.loads(extra_data)
                except json.JSONDecodeError:
                    extra_data = {}
            
            # 获取实名认证信息（从realNameAuth键下获取）
            auth_info = extra_data.get("realNameAuth", {})
            
            if not auth_info:
                return {
                    "status": "success",
                    "data": {
                        "userid": userid,
                        "username": username,
                        "verified": False,
                        "message": "未进行实名认证"
                    }
                }
            
            return {
                "status": "success",
                "data": {
                    "userid": userid,
                    "username": username,
                    "verified": auth_info.get("verified", False),
                    "authStatus": auth_info.get("status", 0),
                    "authTime": auth_info.get("authTime"),
                    "realName": auth_info.get("name", ""),
                    "idCardNo": auth_info.get("idCardNo", ""),  # 已经是隐私保护后的
                    "flowId": auth_info.get("flowId", ""),
                    "message": "认证通过" if auth_info.get("verified") else "未认证或认证未通过"
                }
            }
            
    except Exception as e:
        logger.error(f"查询实名认证状态失败: {str(e)}")
        return {"status": "error", "message": f"查询失败: {str(e)}"}
    finally:
        conn.close()




# 添加用户 - 修改为使用Request接收JSON
@app.post("/users/")
async def add_user(request: Request):
    data = await request.json()
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 设置默认值
            default_values = {
                "userPhone": data.get("userPhone", ""),
                "userCode": data.get("userCode", 0),  # 默认为普通用户
                "username": data.get("username", f"用户{data.get('userPhone', '')[-4:] if data.get('userPhone') else ''}"),
                "userAvatarUrl": data.get("userAvatarUrl", "http://app.playtruly.cn/system/avatar.jpg"),
                "userData": data.get("userData", {"registeredVia": "api"}),
                "userAge": data.get("userAge", 18),
                "userGender": data.get("userGender", "未知"),
                "userSignature": data.get("userSignature", "这个人很懒，什么都没留下"),
                "userTags": data.get("userTags", ["新用户"]),
                "userLikes": data.get("userLikes", 0),
                "userFollowers": data.get("userFollowers", 0),
                "userPoints": data.get("userPoints", 0),  # 给新用户一些初始积分
                "userHobbies": data.get("userHobbies", []),
                "userCity": data.get("userCity", "未知"),
                "userImgUrls": data.get("userImgUrls", ["http://app.playtruly.cn/system/1.jpg", "http://app.playtruly.cn/system/2.jpg"]),
                "userFrequentPlaces": data.get("userFrequentPlaces", "未知"),
                "userBirthday": data.get("userBirthday"),
                "userLevel": data.get("userLevel", 0)
            }
            
            # 处理JSON字段
            json_fields = ["userData", "userTags", "userHobbies", "userImgUrls"]
            for field in json_fields:
                if field in default_values:
                    # 确保值是JSON字符串
                    if isinstance(default_values[field], str):
                        try:
                            # 尝试解析JSON字符串
                            json_obj = json.loads(default_values[field])
                            default_values[field] = json.dumps(json_obj, ensure_ascii=False)
                        except json.JSONDecodeError:
                            # 如果不是有效的JSON字符串，则将其作为普通字符串处理
                            default_values[field] = json.dumps(default_values[field], ensure_ascii=False)
                    else:
                        # 如果是Python对象，直接转换为JSON字符串
                        default_values[field] = json.dumps(default_values[field], ensure_ascii=False)
            
            sql = """
            INSERT INTO users (
                userPhone, userCode, username, userAvatarUrl, userData, 
                userAge, userGender, userSignature, userTags, userLikes, 
                userFollowers, userPoints, userHobbies, userCity, 
                userImgUrls, userFrequentPlaces, userBirthday, userLevel
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
            """
            cursor.execute(sql, (
                default_values["userPhone"],
                default_values["userCode"],
                default_values["username"],
                default_values["userAvatarUrl"],
                default_values["userData"],
                default_values["userAge"],
                default_values["userGender"],
                default_values["userSignature"],
                default_values["userTags"],
                default_values["userLikes"],
                default_values["userFollowers"],
                default_values["userPoints"],
                default_values["userHobbies"],
                default_values["userCity"],
                default_values["userImgUrls"],
                default_values["userFrequentPlaces"],
                default_values["userBirthday"],
                default_values["userLevel"]
            ))
            conn.commit()
            return {"status": "success", "message": "用户添加成功", "user_id": cursor.lastrowid}
    except Exception as e:
        return {"status": "error", "message": f"添加用户失败: {str(e)}"}
    finally:
        conn.close()





        

# 修改query_user函数 - 保持原有JSON格式但优化返回结构
@app.post("/users/query/")
async def query_user(request: Request):
    data = await request.json()
    username = data.get("username")
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            sql = "SELECT * FROM users WHERE username = %s"
            cursor.execute(sql, (username,))
            result = cursor.fetchone()
            return {"status": "success", "data": result} if result else {"status": "error", "message": "用户不存在"}
    finally:
        conn.close()

# 查询所有用户
@app.post("/users/all/")
async def get_all_users():
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            sql = "SELECT * FROM users"
            cursor.execute(sql)
            return {"data": cursor.fetchall()}
    finally:
        conn.close()

@app.post("/users/register_with_phone/")
async def register_with_phone(request: Request):
    data = await request.json()
    phone = data.get("phone")
    verify_code = data.get("verify_code")
    conn = get_conn()
    
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 先检查手机号是否存在
            check_sql = "SELECT * FROM users WHERE phone = %s"
            cursor.execute(check_sql, (phone,))
            user = cursor.fetchone()
            
            if not user:
                return {"status": "error", "message": "无此手机号用户"}
            
            # 更新验证码
            update_sql = "UPDATE users SET verify_code = %s WHERE phone = %s"
            cursor.execute(update_sql, (verify_code, phone))
            conn.commit()
            
            return {"status": "success", "message": "验证码更新成功"}
    finally:
        conn.close()

@app.post("/users/get_verify_code/")
async def get_verify_code(request: Request):
    data = await request.json()
    phone = data.get("phone")
    conn = get_conn()
    
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            sql = "SELECT verify_code FROM users WHERE phone = %s"
            cursor.execute(sql, (phone,))
            result = cursor.fetchone()
            
            if result and result["verify_code"]:
                return {"status": "success", "verify_code": result["verify_code"]}
            else:
                return {"status": "error", "message": "未找到验证码或用户不存在"}
    finally:
        conn.close()


# 图片上传路由
@app.post("/users/upload_image/")
async def upload_image(
    request: Request,
    user_id: int = Form(...),
    file: UploadFile = File(...)
):
    try:
        # 创建用户图片目录
        upload_dir = f"C:/wwwroot/app.playtruly.cn/images/user/{user_id}"
        os.makedirs(upload_dir, exist_ok=True)
        
        # 生成唯一文件名：时间戳+随机值+文件后缀
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        random_str = uuid.uuid4().hex[:8]
        file_ext = os.path.splitext(file.filename)[1]
        filename = f"{timestamp}_{random_str}{file_ext}"
        
        # 保存文件路径
        file_path = os.path.join(upload_dir, filename)
        
        # 保存文件到本地
        with open(file_path, "wb") as buffer:
            buffer.write(await file.read())
        
        # 构建访问URL
        image_url = f"http://app.playtruly.cn/images/user/{user_id}/{filename}"
        
        return {
            "status": "success",
            "message": "图片上传成功",
            "image_url": image_url,
            "user_id": user_id
        }
            
    except Exception as e:
        return {"status": "error", "message": f"图片上传失败: {str(e)}"}


# 0. 更新用户数据
# 0. 更新用户数据
@app.post("/users/update/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def update_user(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    phone = data.get("userPhone")
    email = data.get("userEmail")
    
    if not phone and not email:
        return {"status": "error", "message": "请提供手机号码或邮箱"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 检查是使用手机号还是邮箱查询
            if phone:
                check_sql = "SELECT * FROM users WHERE userPhone = %s"
                cursor.execute(check_sql, (phone,))
                user = cursor.fetchone()
                query_field = "userPhone"
                query_value = phone
            else:
                check_sql = "SELECT * FROM users WHERE userEmail = %s"
                cursor.execute(check_sql, (email,))
                user = cursor.fetchone()
                query_field = "userEmail"
                query_value = email
            
            if not user:
                return {"status": "error", "message": f"无此{'手机号' if phone else '邮箱'}用户"}
            
            # 构建更新SQL语句
            update_fields = []
            update_values = []
            
            # 遍历传入的数据，构建更新字段和值
            for key, value in data.items():
                # 跳过查询条件字段
                if key != query_field:
                    # 特殊处理JSON字段
                    if key in ["userHobbies", "userTags", "userImgUrls", "userData", "extraData"]:
                        try:
                            # 确保值是有效的JSON
                            if isinstance(value, str):
                                value = json.loads(value)
                            update_values.append(json.dumps(value, ensure_ascii=False))
                        except:
                            update_values.append(json.dumps([]))  # 如果无效则设为空数组
                    else:
                        update_values.append(value)
                    update_fields.append(f"{key} = %s")
            
            # 如果没有需要更新的字段，返回错误
            if not update_fields:
                return {"status": "error", "message": "未提供任何需要更新的字段"}
            
            # 构建完整的更新SQL
            update_sql = f"UPDATE users SET {', '.join(update_fields)} WHERE {query_field} = %s"
            update_values.append(query_value)  # 添加WHERE条件的值
            
            # 执行更新
            cursor.execute(update_sql, update_values)
            conn.commit()
            
            # 检查是否有行被更新
            if cursor.rowcount > 0:
                # 获取更新后的用户数据
                cursor.execute(check_sql, (query_value,))
                updated_user = cursor.fetchone()
                
                return {
                    "status": "success", 
                    "message": "用户数据更新成功",
                    "affected_rows": cursor.rowcount,
                    "data": updated_user
                }
            else:
                return {"status": "warning", "message": "数据未发生变化"}
                
    except Exception as e:
        return {"status": "error", "message": f"更新用户数据失败: {str(e)}"}
    finally:
        conn.close()


# 1. 查询手机是否已被注册路由
@app.post("/users/check_phone/")
@app.post("/users/check_account/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def check_account(request: Request):
    
    data = get_decrypted_data(request)  # 获取解密后的数据
    phone = data.get("userPhone")
    email = data.get("userEmail")
    
    if not phone and not email:
        return {"status": "error", "message": "请提供手机号码或邮箱地址"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            result = None
            account_type = None
            message = ""
            
            # 优先检查手机号
            if phone:
                sql_phone = "SELECT * FROM users WHERE userPhone = %s"
                cursor.execute(sql_phone, (phone,))
                result = cursor.fetchone()
                if result:
                    account_type = "phone"
                    message = "该手机号已注册"
            
            # 如果手机号未找到且提供了邮箱，则检查邮箱
            if not result and email:
                sql_email = "SELECT * FROM users WHERE userEmail = %s"
                cursor.execute(sql_email, (email,))
                result = cursor.fetchone()
                if result:
                    account_type = "email"
                    message = "该邮箱已注册"
            
            if result:
                # 处理JSON字段，将JSON字符串转换为Python对象
                json_fields = ["userData", "userTags", "userHobbies", "userImgUrls", "extraData"]
                for field in json_fields:
                    if field in result and result[field]:
                        try:
                            result[field] = json.loads(result[field])
                        except (json.JSONDecodeError, TypeError):
                            # 如果解析失败，保持原样
                            pass
                
                return {
                    "status": "success", 
                    "exists": True, 
                    "message": message,
                    "account_type": account_type,
                    "data": result
                }
            else:
                message = ""
                if phone and email:
                    message = "该手机号和邮箱均未注册"
                elif phone:
                    message = "该手机号未注册"
                elif email:
                    message = "该邮箱未注册"
                
                return {
                    "status": "success", 
                    "exists": False, 
                    "message": message
                }
    except Exception as e:
        return {"status": "error", "message": f"查询失败: {str(e)}"}
    finally:
        conn.close()




# 2. 添加短信验证码发送路由（使用ihuyi服务）
@app.post("/users/send_sms_code/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def send_sms_code(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    phone = data.get("userPhone")
    code = data.get("userCode")
    
    if not phone or not code:
        return {"status": "error", "message": "请提供手机号码和验证码"}
    
    # ihuyi短信API配置
    account = "C16014897"
    password = "7d99a6e4e08fe8950127b0984d7d0f79"
    url = 'http://106.ihuyi.com/webservice/sms.php?method=Submit'
    
    # 构建短信内容
    content = f'您的验证码是：{code}。请不要把验证码泄露给其他人。'
    
    # 准备请求参数
    values = {
        'account': account,
        'password': password,
        'mobile': phone,
        'content': content,
        'format': 'json',
    }
    
    try:
        import urllib.parse
        import urllib.request
        
        # 将数据进行编码
        data_encoded = urllib.parse.urlencode(values).encode(encoding='UTF8')
        
        # 发起请求
        req = urllib.request.Request(url, data_encoded)
        response = urllib.request.urlopen(req, timeout=30)
        res = response.read()
        
        # 解析响应结果
        result = res.decode("utf8")
        
        # 尝试解析JSON响应
        try:
            import json
            response_data = json.loads(result)
        except:
            response_data = {"raw_response": result}
        
        # 同时更新数据库中的验证码
        conn = get_conn()
        try:
            with conn.cursor() as cursor:
                # 检查用户是否存在
                check_sql = "SELECT * FROM users WHERE userPhone = %s"
                cursor.execute(check_sql, (phone,))
                user = cursor.fetchone()
                
                if user:
                    # 更新验证码
                    update_sql = "UPDATE users SET userCode = %s WHERE userPhone = %s"
                    cursor.execute(update_sql, (code, phone))
                    conn.commit()
                
                return {
                    "status": "success", 
                    "message": "验证码已发送",
                    "api_response": response_data,
                    "phone": phone
                }
        finally:
            conn.close()
            
    except urllib.error.URLError as e:
        return {"status": "error", "message": f"网络请求失败: {str(e)}"}
    except urllib.error.HTTPError as e:
        return {"status": "error", "message": f"HTTP请求失败: {str(e)}"}
    except Exception as e:
        return {"status": "error", "message": f"发送验证码失败: {str(e)}"}



# 2. 添加短信验证码发送路由
@app.post("/users/send_sms_code_TX/")
async def send_sms_code(request: Request):
    data = await request.json()
    phone = data.get("userPhone")
    code = data.get("userCode")
    
    if not phone or not code:
        return {"status": "error", "message": "请提供手机号码和验证码"}
    
    # 腾讯云短信API配置
    secret_id = "AKIDjOuLZcS5e1B9tPi8NflgPL9kANQGsGSQ"  # 请替换为实际的密钥
    secret_key = "sWxCJCrKaeU6WjiU3UjlZCcrshKkvshN"     # 请替换为实际的密钥
    
    service = "sms"
    host = "sms.tencentcloudapi.com"
    endpoint = "https://" + host
    region = "ap-guangzhou"
    action = "SendSms"
    version = "2021-01-11"
    algorithm = "TC3-HMAC-SHA256"
    timestamp = int(time.time())
    date = datetime.utcfromtimestamp(timestamp).strftime("%Y-%m-%d")
    
    # 短信参数
    params = {
        "SmsSdkAppId": "1400975561",  # 请替换为实际的应用ID
        "SignName": "武汉玩真的科技有限公司",         # 请替换为实际的签名
        "TemplateId": "2400909",         # 请替换为实际的模板ID
        "TemplateParamSet": [str(code)],
        "PhoneNumberSet": ["+86" + phone],
        "SessionContext": "验证码发送"
    }
    
    # 构建请求
    http_request_method = "POST"
    canonical_uri = "/"
    canonical_querystring = ""
    ct = "application/json; charset=utf-8"
    payload = json.dumps(params)
    canonical_headers = "content-type:%s\nhost:%s\n" % (ct, host)
    signed_headers = "content-type;host"
    hashed_request_payload = hashlib.sha256(payload.encode("utf-8")).hexdigest()
    canonical_request = (http_request_method + "\n" +
                        canonical_uri + "\n" +
                        canonical_querystring + "\n" +
                        canonical_headers + "\n" +
                        signed_headers + "\n" +
                        hashed_request_payload)
    
    # 计算签名
    credential_scope = date + "/" + service + "/" + "tc3_request"
    hashed_canonical_request = hashlib.sha256(canonical_request.encode("utf-8")).hexdigest()
    string_to_sign = (algorithm + "\n" +
                    str(timestamp) + "\n" +
                    credential_scope + "\n" +
                    hashed_canonical_request)
    
    # 签名函数
    def sign(key, msg):
        return hmac.new(key, msg.encode("utf-8"), hashlib.sha256).digest()
    
    secret_date = sign(("TC3" + secret_key).encode("utf-8"), date)
    secret_service = sign(secret_date, service)
    secret_signing = sign(secret_service, "tc3_request")
    signature = hmac.new(secret_signing, string_to_sign.encode("utf-8"), hashlib.sha256).hexdigest()
    
    # 构建授权信息
    authorization = (algorithm + " " +
                    "Credential=" + secret_id + "/" + credential_scope + ", " +
                    "SignedHeaders=" + signed_headers + ", " +
                    "Signature=" + signature)
    
    # 发送请求
    headers = {
        "Authorization": authorization,
        "Content-Type": "application/json; charset=utf-8",
        "Host": host,
        "X-TC-Action": action,
        "X-TC-Timestamp": str(timestamp),
        "X-TC-Version": version,
        "X-TC-Region": region
    }
    
    try:
        # 发送HTTP请求
        response = requests.post(endpoint, headers=headers, data=payload)
        resp_data = response.json()
        
        # 同时更新数据库中的验证码
        conn = get_conn()
        try:
            with conn.cursor() as cursor:
                # 检查用户是否存在
                check_sql = "SELECT * FROM users WHERE userPhone = %s"
                cursor.execute(check_sql, (phone,))
                user = cursor.fetchone()
                
                if user:
                    # 更新验证码
                    update_sql = "UPDATE users SET userCode = %s WHERE userPhone = %s"
                    cursor.execute(update_sql, (code, phone))
                    conn.commit()
                
                return {
                    "status": "success", 
                    "message": "验证码已发送",
                    "api_response": resp_data
                }
        finally:
            conn.close()
            
    except Exception as e:
        return {"status": "error", "message": f"发送验证码失败: {str(e)}"}





# 邮箱验证码发送路由
@app.post("/users/send_email_code/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def send_email_code(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    email = data.get("userEmail")  # 获取用户邮箱
    code = data.get("userCode")    # 获取验证码
    
    if not email or not code:
        return {"status": "error", "message": "请提供邮箱地址和验证码"}
    
    # 邮件服务器配置（以QQ邮箱为例）
    email_host = "smtp.qq.com"         # QQ邮箱SMTP服务器地址
    email_port = 465                   # QQ邮箱SMTP服务器SSL端口
    email_sender = "leftnorth@foxmail.com"    # 发件人邮箱
    email_pwd = "cfwdhrnvlhsvbhci"     # 发件人邮箱授权码
    
    # 邮件内容
    subject = "【玩真的】验证码"  # 邮件主题
    # 构建HTML格式的邮件内容，可以根据需求自定义样式
    html_content = f"""
    <html>
    <body>
        <div style="background-color:#f9f9f9; padding:20px;">
            <div style="max-width:600px; margin:0 auto; background-color:white; padding:20px; border-radius:5px; box-shadow:0 0 10px rgba(0,0,0,0.1);">
                <h2 style="color:#333; text-align:center;">玩真的-验证码</h2>
                <p style="font-size:16px; color:#555;">您好，</p>
                <p style="font-size:16px; color:#555;">您的验证码是：</p>
                <div style="text-align:center; margin:30px 0;">
                    <span style="font-size:28px; font-weight:bold; color:#4a90e2; letter-spacing:5px; padding:10px 20px; background-color:#f5f5f5; border-radius:5px;">{code}</span>
                </div>
                <p style="font-size:16px; color:#555;">验证码有效期为10分钟，请勿泄露给他人。</p>
                <p style="font-size:16px; color:#555;">如非本人操作，请忽略此邮件。</p>
                <p style="font-size:14px; color:#999; text-align:center; margin-top:40px;">此邮件由系统自动发送，请勿回复</p>
            </div>
        </div>
    </body>
    </html>
    """
    
    try:
        
        # 创建邮件对象
        message = MIMEMultipart()
        # 使用email.utils中的formataddr函数来正确格式化发件人地址
        from email.utils import formataddr
        message['From'] = formataddr(("玩真的", email_sender))  # 这会正确处理中文编码
        message['To'] = email  # 直接使用收件人邮箱地址
        message['Subject'] = Header(subject, 'utf-8')  # 邮件主题
        
        # 添加HTML内容
        message.attach(MIMEText(html_content, 'html', 'utf-8'))
        
        # 连接邮件服务器并发送邮件
        try:
            # 使用SSL加密连接
            smtp_obj = smtplib.SMTP_SSL(email_host, email_port)
            # 显示连接信息（调试用，可选）
            # smtp_obj.set_debuglevel(1)
            # 登录邮箱
            smtp_obj.login(email_sender, email_pwd)
            # 发送邮件
            smtp_obj.sendmail(email_sender, [email], message.as_string())
            # 关闭连接
            smtp_obj.quit()
            
            # 更新数据库中的验证码
            conn = get_conn()
            try:
                with conn.cursor() as cursor:
                    # 检查用户是否存在
                    check_sql = "SELECT * FROM users WHERE userEmail = %s"
                    cursor.execute(check_sql, (email,))
                    user = cursor.fetchone()
                    
                    if user:
                        # 更新验证码
                        update_sql = "UPDATE users SET userCode = %s, codeExpireTime = DATE_ADD(NOW(), INTERVAL 10 MINUTE) WHERE userEmail = %s"
                        cursor.execute(update_sql, (code, email))
                        conn.commit()
                        update_message = "验证码已更新"
                    else:
                        # 如果用户不存在，可以创建一个临时记录或者返回错误
                        # 这里选择创建一个临时记录
                        insert_sql = """
                        INSERT INTO users (userEmail, userCode, codeExpireTime, userStatus) 
                        VALUES (%s, %s, DATE_ADD(NOW(), INTERVAL 10 MINUTE), 0)
                        """
                        cursor.execute(insert_sql, (email, code))
                        conn.commit()
                        update_message = "临时用户记录已创建"
                    
                    return {
                        "status": "success", 
                        "message": f"验证码已发送至邮箱 {email}",
                        "db_update": update_message
                    }
            except Exception as db_error:
                # 数据库操作失败
                # return {"status": "partial_success", "message": f"虽然该用户没有注册，但是我还是给他发送了邮件。: {str(db_error)}"}
                return {"status": "success", "message": f"验证码已发送至邮箱 {email}"}
            finally:
                conn.close()
                
        except smtplib.SMTPException as smtp_error:
            # SMTP操作失败
            return {"status": "error", "message": f"发送邮件失败: {str(smtp_error)}"}
            
    except Exception as e:
        # 其他未知错误
        return {"status": "error", "message": f"验证码发送过程中发生错误: {str(e)}"}





# 3. 注册用户路由
@app.post("/users/create_with_phone/")
@app.post("/users/create_account/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def create_account(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    GenToken = data.get("GenToken")
    phone = data.get("userPhone")
    email = data.get("userEmail")
    code = data.get("userCode")
    
    # 检查是否提供了手机号或邮箱
    if not phone and not email:
        return {"status": "error", "message": "请提供手机号码或邮箱地址"}
    
    # 设置用于生成token的标识符（优先使用手机号）
    # 为融云API处理userId - 对邮箱地址进行编码
    if phone:
        user_identifier = phone  # 手机号可以直接使用
    else:
        # 为邮箱创建一个符合融云规范的userId
        # 选项1: 使用MD5哈希处理邮箱地址
        user_identifier = f"email_{hashlib.md5(email.encode()).hexdigest()[:16]}"
        # 或者选项2: 使用Base64编码替换特殊字符
        # import base64
        # user_identifier = f"email_{base64.b64encode(email.encode()).decode().replace('=','').replace('+','_').replace('/','.')}"
    
    if GenToken:
        # 调用融云API获取token
        try:
            # 融云API配置
            app_key = "x4vkb1qpx8r4k"
            app_secret = "UCbIqxWy9XMo"  # 需要替换为实际的App Secret
            nonce = str(random.randint(1000000000, 9999999999))
            timestamp = str(int(time.time()))
            
            # 计算签名
            signature_str = app_secret + nonce + timestamp
            signature = hashlib.sha1(signature_str.encode('utf-8')).hexdigest()
            
            # 构建请求
            url = "https://api.rong-api.com/user/getToken.json"
            headers = {
                "App-Key": app_key,
                "Nonce": nonce,
                "Timestamp": timestamp,
                "Signature": signature,
                "Content-Type": "application/x-www-form-urlencoded"
            }
            
            # 使用处理后的标识符作为userId
            payload = {"userId": user_identifier}
            
            # 发送请求
            response = requests.post(url, headers=headers, data=payload)
            resp_data = response.json()
            
            if resp_data.get("code") == 200:
                # 如果成功获取token，将其存储到extraData中
                if "extraData" not in data:
                    data["extraData"] = {}
                elif isinstance(data["extraData"], str):
                    try:
                        data["extraData"] = json.loads(data["extraData"])
                    except:
                        data["extraData"] = {}
                
                # 确保extraData是字典类型
                if not isinstance(data["extraData"], dict):
                    data["extraData"] = {}
                
                # 存储token和用于融云的userId
                data["extraData"]["token"] = resp_data.get("token")
                data["extraData"]["rongcloud_user_id"] = user_identifier  # 存储处理后的userId，以便登录时使用
            else:
                return {"status": "error", "message": f"获取融云token失败: {resp_data}"}
        except Exception as e:
            return {"status": "error", "message": f"获取融云token失败: {str(e)}"}

    # 如果未提供userCode，默认设置为0（普通用户）
    if code is None:
        code = 0
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 检查手机号和邮箱是否已存在
            if phone:
                check_phone_sql = "SELECT * FROM users WHERE userPhone = %s"
                cursor.execute(check_phone_sql, (phone,))
                existing_user_by_phone = cursor.fetchone()
                if existing_user_by_phone:
                    return {"status": "error", "message": "该手机号已注册"}
            
            if email:
                check_email_sql = "SELECT * FROM users WHERE userEmail = %s"
                cursor.execute(check_email_sql, (email,))
                existing_user_by_email = cursor.fetchone()
                if existing_user_by_email:
                    return {"status": "error", "message": "该邮箱已注册"}
            
            # 生成默认用户名（基于手机号或邮箱）
            default_username = ""
            if phone:
                default_username = f"用户{phone[-4:]}"  # 使用手机号后4位
            elif email:
                # 截取邮箱@前的部分作为用户名基础
                email_prefix = email.split('@')[0]
                # 如果过长则截取
                if len(email_prefix) > 8:
                    email_prefix = email_prefix[:8]
                default_username = f"用户{email_prefix}"
            
            # 设置默认值
            default_values = {
                "username": default_username,
                "userAvatarUrl": "http://app.playtruly.cn/system/avatar.jpg",
                "userData": {"registeredVia": "phone" if phone else "email"},
                "userAge": 18,
                "userGender": "未知",
                "userSignature": "这个人很懒，什么都没留下",
                "userTags": ["新用户"],
                "userLikes": 0,
                "userFollowers": 0,
                "userPoints": 100,  # 修改为100积分
                "userHobbies": [],
                "userCity": "未知",
                "userImgUrls": ["http://app.playtruly.cn/system/1.jpg", "http://app.playtruly.cn/system/2.jpg"],
                "userFrequentPlaces": "未知",
                "userBirthday": None,
                "userLevel": 0,
                "extraData": []
            }
            
            # 用户可以在请求中覆盖默认值
            for key in default_values:
                if key in data and data[key] is not None:
                    default_values[key] = data[key]
            
            # 处理JSON字段
            json_fields = ["userData", "userTags", "userHobbies", "userImgUrls", "extraData"]
            for field in json_fields:
                if field in default_values:
                    # 确保值是JSON字符串
                    if isinstance(default_values[field], str):
                        try:
                            # 尝试解析JSON字符串
                            json_obj = json.loads(default_values[field])
                            default_values[field] = json.dumps(json_obj, ensure_ascii=False)
                        except json.JSONDecodeError:
                            # 如果不是有效的JSON字符串，则将其作为普通字符串处理
                            default_values[field] = json.dumps(default_values[field], ensure_ascii=False)
                    else:
                        # 如果是Python对象，直接转换为JSON字符串
                        default_values[field] = json.dumps(default_values[field], ensure_ascii=False)
            
            # 构建SQL插入语句 - 字段列表根据是否提供手机号和邮箱动态构建
            fields = ["userCode"] + list(default_values.keys())
            
            # 添加手机号和邮箱字段（如果提供）
            if phone:
                fields.append("userPhone")
            if email:
                fields.append("userEmail")
            
            placeholders = ", ".join(["%s"] * len(fields))
            field_str = ", ".join(fields)
            
            insert_sql = f"INSERT INTO users ({field_str}) VALUES ({placeholders})"
            
            # 构建参数值 - 先添加基本字段，再添加手机号/邮箱
            values = [code] + list(default_values.values())
            if phone:
                values.append(phone)
            if email:
                values.append(email)
            
            # 执行插入
            cursor.execute(insert_sql, values)
            conn.commit()
            
            # 获取新创建的用户信息
            new_user_id = cursor.lastrowid
            
            # 添加积分变更记录
            points_log_sql = """
            INSERT INTO user_points_log 
            (userid, points_change, points_before, points_after, change_type, change_reason, related_id, operator, status, created_at) 
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """
            cursor.execute(points_log_sql, (
                new_user_id,  # userid
                100,         # points_change (修改为1000)
                0,            # points_before
                100,         # points_after (修改为1000)
                6,            # change_type (数字类型，7表示注册赠送)
                "新用户注册赠送积分",  # change_reason
                new_user_id,  # related_id
                "系统自动赠送",  # operator
                "有效"         # status (新增字段)
            ))
            conn.commit()  # 添加这行来提交积分日志的插入操作

            
            get_user_sql = "SELECT * FROM users WHERE userid = %s"
            cursor.execute(get_user_sql, (new_user_id,))
            new_user = cursor.fetchone()
            
            # 处理JSON字段，将JSON字符串转换为Python对象
            if new_user:
                json_fields = ["userData", "userTags", "userHobbies", "userImgUrls", "extraData"]
                for field in json_fields:
                    if field in new_user and new_user[field]:
                        try:
                            new_user[field] = json.loads(new_user[field])
                        except (json.JSONDecodeError, TypeError):
                            # 如果解析失败，保持原样
                            pass
            
            return {
                "status": "success", 
                "message": "用户创建成功", 
                "user_id": new_user_id,
                "data": new_user,
                "registration_method": "phone" if phone else "email"
            }
    except Exception as e:
        return {"status": "error", "message": f"创建用户失败: {str(e)}"}
    finally:
        conn.close()


# 4. 带密码的用户注册路由
@app.post("/users/register_with_password/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def register_with_password(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    phone = data.get("userPhone")
    password = data.get("password")
    user_code = data.get("userCode") # 用户身份代码
    gen_token = data.get("GenToken") # 是否生成融云token

    if not phone:
        return {"status": "error", "message": "请提供手机号码"}
    if not password:
        return {"status": "error", "message": "请提供密码"}

    # 如果未提供userCode，默认设置为0（普通用户）
    if user_code is None:
        user_code = 0
        
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 检查手机号是否已存在
            check_sql = "SELECT * FROM users WHERE userPhone = %s"
            cursor.execute(check_sql, (phone,))
            existing_user = cursor.fetchone()
            if existing_user:
                return {"status": "error", "message": "该手机号已注册"}

            # 设置用户默认值
            default_values = {
                "username": data.get("username", f"用户{phone[-4:]}"),
                "userAvatarUrl": data.get("userAvatarUrl", "http://app.playtruly.cn/system/avatar.jpg"),
                "userData": data.get("userData", {"registeredVia": "password"}), # 标记注册方式
                "userAge": data.get("userAge", 18),
                "userGender": data.get("userGender", "未知"),
                "userSignature": data.get("userSignature", "这个人很懒，什么都没留下"),
                "userTags": data.get("userTags", ["新用户"]),
                "userLikes": data.get("userLikes", 0),
                "userFollowers": data.get("userFollowers", 0),
                "userPoints": data.get("userPoints", 100),  # 修改为1000积分
                "userHobbies": data.get("userHobbies", []),
                "userCity": data.get("userCity", "未知"),
                "userImgUrls": data.get("userImgUrls", ["http://app.playtruly.cn/system/1.jpg", "http://app.playtruly.cn/system/2.jpg"]),
                "userFrequentPlaces": data.get("userFrequentPlaces", "未知"),
                "userBirthday": data.get("userBirthday"),
                "userLevel": data.get("userLevel", 0),
                "extraData": data.get("extraData", {}) # 初始化为字典，用于存储融云token等
            }
            
            # 用户可以在请求中覆盖默认值
            for key, value in default_values.items():
                if key in data and data[key] is not None:
                    default_values[key] = data[key]
            
            # 如果需要生成融云token
            if gen_token:
                try:
                    app_key = "x4vkb1qpx8r4k" # 替换为你的 App Key
                    app_secret = "UCbIqxWy9XMo"  # 替换为你的 App Secret
                    nonce = str(random.randint(1000000000, 9999999999))
                    timestamp = str(int(time.time()))
                    signature_str = app_secret + nonce + timestamp
                    signature = hashlib.sha1(signature_str.encode('utf-8')).hexdigest()
                    
                    rong_url = "https://api.rong-api.com/user/getToken.json"
                    rong_headers = {
                        "App-Key": app_key, "Nonce": nonce, "Timestamp": timestamp,
                        "Signature": signature, "Content-Type": "application/x-www-form-urlencoded"
                    }
                    rong_payload = {"userId": phone, "name": default_values["username"], "portraitUri": default_values["userAvatarUrl"]}
                    
                    response = requests.post(rong_url, headers=rong_headers, data=rong_payload)
                    resp_data = response.json()
                    
                    if resp_data.get("code") == 200:
                        # 确保 extraData 是字典
                        if not isinstance(default_values["extraData"], dict):
                            try:
                                default_values["extraData"] = json.loads(default_values["extraData"]) if isinstance(default_values["extraData"], str) else {}
                            except:
                                default_values["extraData"] = {}
                        default_values["extraData"]["rongCloudToken"] = resp_data.get("token")
                    else:
                        # 注意：如果获取token失败，这里可以选择是否继续注册，或返回错误
                        # 为了与 create_with_phone 行为一致，这里返回错误
                        return {"status": "error", "message": f"获取融云token失败: {resp_data.get('errorMessage', resp_data)}"}
                except Exception as e:
                    return {"status": "error", "message": f"获取融云token时发生异常: {str(e)}"}

            # 处理JSON字段，确保它们以JSON字符串形式存入数据库
            json_fields = ["userData", "userTags", "userHobbies", "userImgUrls", "extraData"]
            for field in json_fields:
                if field in default_values:
                    if not isinstance(default_values[field], str):
                        default_values[field] = json.dumps(default_values[field], ensure_ascii=False)
                    else: # 如果已经是字符串，尝试解析再序列化，确保格式统一
                        try:
                            parsed_json = json.loads(default_values[field])
                            default_values[field] = json.dumps(parsed_json, ensure_ascii=False)
                        except json.JSONDecodeError:
                             # 如果不是有效的JSON字符串，则将其作为普通字符串处理（或根据业务决定是否报错）
                             # 这里我们假设它可能是一个非JSON字符串，直接序列化它
                            default_values[field] = json.dumps(default_values[field], ensure_ascii=False)


            # 创建用户
            user_fields = ["userPhone", "userCode"] + list(default_values.keys())
            user_placeholders = ", ".join(["%s"] * len(user_fields))
            user_field_str = ", ".join(user_fields)
            insert_user_sql = f"INSERT INTO users ({user_field_str}) VALUES ({user_placeholders})"
            user_values = [phone, user_code] + list(default_values.values())
            
            cursor.execute(insert_user_sql, user_values)
            new_user_id = cursor.lastrowid # 获取新用户的ID

            # 哈希密码并存储
            salt_hex, password_hash_hex = hash_password(password)
            insert_password_sql = """
            INSERT INTO user_passwords (userid, password_hash, salt, algorithm, iterations, last_changed) 
            VALUES (%s, %s, %s, %s, %s, %s)
            """
            # 'pbkdf2_sha256' 和 260000 是表定义的默认值
            password_values = (new_user_id, password_hash_hex, salt_hex, 'pbkdf2_sha256', 260000, datetime.now())
            cursor.execute(insert_password_sql, password_values)
            
            # 添加积分变更记录
            points_log_sql = """
            INSERT INTO user_points_log 
            (userid, points_change, points_before, points_after, change_type, change_reason, related_id, operator, remark, created_at) 
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """
            cursor.execute(points_log_sql, (
                new_user_id,  # userid
                100,         # points_change
                0,            # points_before
                100,         # points_after
                7,            # change_type (数字类型，7表示注册赠送)
                '新用户注册赠送积分',  # change_reason
                new_user_id,  # related_id
                '系统',        # operator
                '系统自动赠送'  # remark (修正字段名)
            ))
            
            conn.commit()

            # 获取新创建的用户完整信息（包括自动生成的字段如userid, created_at）
            cursor.execute(check_sql, (phone,)) # 使用之前的 check_sql
            new_user_details = cursor.fetchone()

            # 处理从数据库读出的JSON字段，转换为Python对象
            if new_user_details:
                json_db_fields = ["userData", "userTags", "userHobbies", "userImgUrls", "extraData"]
                for field in json_db_fields:
                    if field in new_user_details and new_user_details[field]:
                        try:
                            new_user_details[field] = json.loads(new_user_details[field])
                        except (json.JSONDecodeError, TypeError):
                            pass # 解析失败则保持原样或记录日志

            return {
                "status": "success", 
                "message": "用户注册成功", 
                "user_id": new_user_id,
                "data": new_user_details
            }
    except pymysql.err.IntegrityError as ie: # 更具体的异常捕获，例如唯一键冲突
        conn.rollback() # 如果有事务，则回滚
        # 根据错误代码判断是手机号重复还是其他唯一性约束冲突
        if ie.args[0] == 1062: # Duplicate entry
             return {"status": "error", "message": "数据已存在，请检查输入。"}
        return {"status": "error", "message": f"数据库操作失败: {str(ie)}"}
    except Exception as e:
        conn.rollback() # 如果有事务，则回滚
        return {"status": "error", "message": f"创建用户失败: {str(e)}"}
    finally:
        conn.close()





# 5. 用户登录路由
@app.post("/users/login/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def login_user(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    identifier = data.get("identifier") # 可以是手机号或用户名
    password = data.get("password")

    if not identifier or not password:
        return {"status": "error", "message": "请提供登录凭证（手机号/用户名和密码）"}

    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 尝试通过手机号或用户名查找用户
            user_sql = "SELECT * FROM users WHERE userPhone = %s OR username = %s"
            cursor.execute(user_sql, (identifier, identifier))
            user = cursor.fetchone()

            if not user:
                return {"status": "error", "message": "用户不存在"}

            user_id = user['userid']

            # 查找用户密码信息
            password_sql = "SELECT password_hash, salt FROM user_passwords WHERE userid = %s"
            cursor.execute(password_sql, (user_id,))
            password_data = cursor.fetchone()

            if not password_data:
                return {"status": "error", "message": "用户未设置密码或密码信息错误"}

            # 验证密码
            is_valid = verify_password(
                password_data['password_hash'],
                password_data['salt'],
                password
            )

            if not is_valid:
                return {"status": "error", "message": "密码错误"}

            # 登录成功，更新最后登录时间
            update_login_time_sql = "UPDATE users SET last_login = %s WHERE userid = %s"
            cursor.execute(update_login_time_sql, (datetime.now(), user_id))
            conn.commit()
            
            # 处理从数据库读出的JSON字段
            json_db_fields = ["userData", "userTags", "userHobbies", "userImgUrls", "extraData"]
            for field in json_db_fields:
                if field in user and user[field]:
                    try:
                        user[field] = json.loads(user[field])
                    except (json.JSONDecodeError, TypeError):
                        pass

            return {"status": "success", "message": "登录成功", "data": user}
            
    except Exception as e:
        return {"status": "error", "message": f"登录失败: {str(e)}"}
    finally:
        conn.close()

# 6. 修改密码路由
@app.post("/users/change_password/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def change_password(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("userId") # 优先使用userId，如果提供
    identifier = data.get("identifier") # 备用，可以是手机号或用户名
    old_password = data.get("oldPassword")
    new_password = data.get("newPassword")

    if not (user_id or identifier) or not old_password or not new_password:
        return {"status": "error", "message": "请提供用户标识、旧密码和新密码"}
    
    if len(new_password) < 6: # 简单密码策略示例
        return {"status": "error", "message": "新密码长度不能少于6位"}

    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            actual_user_id = None
            if user_id:
                actual_user_id = user_id
                # 验证 user_id 是否存在
                check_user_sql = "SELECT userid FROM users WHERE userid = %s"
                cursor.execute(check_user_sql, (actual_user_id,))
                if not cursor.fetchone():
                    return {"status": "error", "message": "提供的userId无效"}
            elif identifier:
                user_sql = "SELECT userid FROM users WHERE userPhone = %s OR username = %s"
                cursor.execute(user_sql, (identifier, identifier))
                user_identity = cursor.fetchone()
                if not user_identity:
                     return {"status": "error", "message": "用户不存在"}
                actual_user_id = user_identity['userid']
            
            if not actual_user_id: # 双重检查
                 return {"status": "error", "message": "无法确定用户"}


            # 查找用户当前的密码信息
            current_password_sql = "SELECT password_hash, salt FROM user_passwords WHERE userid = %s"
            cursor.execute(current_password_sql, (actual_user_id,))
            password_data = cursor.fetchone()

            if not password_data:
                return {"status": "error", "message": "用户未设置密码或无法找到密码记录"}

            # 验证旧密码
            if not verify_password(password_data['password_hash'], password_data['salt'], old_password):
                return {"status": "error", "message": "旧密码错误"}

            # 哈希新密码 (使用新的盐)
            new_salt_hex, new_password_hash_hex = hash_password(new_password)

            # 更新密码
            update_password_sql = """
            UPDATE user_passwords 
            SET password_hash = %s, salt = %s, last_changed = %s, updated_at = %s
            WHERE userid = %s
            """
            current_time = datetime.now()
            cursor.execute(update_password_sql, (
                new_password_hash_hex, new_salt_hex, current_time, current_time, actual_user_id
            ))
            conn.commit()

            return {"status": "success", "message": "密码修改成功"}

    except Exception as e:
        conn.rollback() # 如果有事务，则回滚
        return {"status": "error", "message": f"修改密码失败: {str(e)}"}
    finally:
        conn.close()



def hash_password(password):
    # 生成随机盐值
    salt = os.urandom(16)
    salt_hex = binascii.hexlify(salt).decode('utf-8')
    
    # 使用 PBKDF2 算法生成哈希
    iterations = 260000
    password_hash = hashlib.pbkdf2_hmac(
        'sha256',
        password.encode('utf-8'),
        salt,
        iterations
    )
    password_hash_hex = binascii.hexlify(password_hash).decode('utf-8')
    
    return salt_hex, password_hash_hex




def verify_password(stored_hash, salt_hex, input_password):
    """
    验证用户输入的密码是否与存储的哈希密码匹配
    
    参数:
        stored_hash: 存储在数据库中的密码哈希(十六进制字符串)
        salt_hex: 存储在数据库中的盐值(十六进制字符串)
        input_password: 用户输入的密码
    
    返回:
        bool: 如果密码匹配返回True，否则返回False
    """
    # 将十六进制盐值转换回字节
    salt = binascii.unhexlify(salt_hex)
    
    # 使用相同的算法和参数计算输入密码的哈希
    iterations = 260000
    input_hash = hashlib.pbkdf2_hmac(
        'sha256',
        input_password.encode('utf-8'),
        salt,
        iterations
    )
    input_hash_hex = binascii.hexlify(input_hash).decode('utf-8')
    
    # 比较计算得到的哈希与存储的哈希是否相同
    return input_hash_hex == stored_hash



# 帮助与反馈路由
@app.post("/feedback/submit/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def submit_feedback(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("userId")
    feedback_content = data.get("feedbackContent")
    
    # 验证必填字段
    if not user_id:
        return {"status": "error", "message": "请提供用户ID"}
    
    if not feedback_content:
        return {"status": "error", "message": "请提供反馈内容"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 获取用户信息
            user_sql = "SELECT * FROM users WHERE userid = %s"
            cursor.execute(user_sql, (user_id,))
            user = cursor.fetchone()
            
            if not user:
                return {"status": "error", "message": "用户不存在"}
            
            # 设置默认值
            default_values = {
                "feedbackType": data.get("feedbackType", "问题反馈"),
                "feedbackTitle": data.get("feedbackTitle", f"用户{user_id}的反馈"),
                "feedbackImages": data.get("feedbackImages", []),
                "feedbackCategory": data.get("feedbackCategory", ["应用问题"]),
                "feedbackTags": data.get("feedbackTags", ["其他"]),
                "feedbackLocation": data.get("feedbackLocation", "未知"),
                "feedbackCoordinates": data.get("feedbackCoordinates", "POINT(0 0)"),
                "userPhone": user.get("userPhone", ""),
                "username": user.get("username", ""),
                "userAvatar": user.get("userAvatarUrl", ""),
                "contactInfo": data.get("contactInfo", user.get("userPhone", "")),
                "appVersion": data.get("appVersion", "1.0.0"),
                "deviceInfo": data.get("deviceInfo", {}),
                "processStatus": 0,  # 默认为未处理
                "status": data.get("status", ["开启"]),
                "extraData": data.get("extraData", {})
            }
            
            # 处理JSON字段
            json_fields = ["feedbackImages", "feedbackCategory", "feedbackTags", "deviceInfo", "status", "extraData"]
            for field in json_fields:
                if field in default_values:
                    # 确保值是JSON字符串
                    if isinstance(default_values[field], str):
                        try:
                            # 尝试解析JSON字符串
                            json_obj = json.loads(default_values[field])
                            default_values[field] = json.dumps(json_obj, ensure_ascii=False)
                        except json.JSONDecodeError:
                            # 如果不是有效的JSON字符串，则将其作为普通字符串处理
                            default_values[field] = json.dumps(default_values[field], ensure_ascii=False)
                    else:
                        # 如果是Python对象，直接转换为JSON字符串
                        default_values[field] = json.dumps(default_values[field], ensure_ascii=False)
            
            # 构建SQL插入语句
            fields = ["userId", "feedbackContent"] + list(default_values.keys())
            placeholders = ", ".join(["%s"] * len(fields))
            field_str = ", ".join(fields)
            
            # 处理坐标字段，需要使用ST_GeomFromText函数
            if "feedbackCoordinates" in fields:
                insert_sql = f"INSERT INTO feedback ({field_str}) VALUES ("
                for i, field in enumerate(fields):
                    if field == "feedbackCoordinates":
                        insert_sql += "ST_GeomFromText(%s)"
                    else:
                        insert_sql += "%s"
                    
                    if i < len(fields) - 1:
                        insert_sql += ", "
                insert_sql += ")"
            else:
                insert_sql = f"INSERT INTO feedback ({field_str}) VALUES ({placeholders})"
            
            # 构建参数值
            values = [user_id, feedback_content] + list(default_values.values())
            
            # 执行插入
            cursor.execute(insert_sql, values)
            conn.commit()
            
            # 获取新创建的反馈信息
            feedback_id = cursor.lastrowid
            get_feedback_sql = "SELECT * FROM feedback WHERE feedbackId = %s"
            cursor.execute(get_feedback_sql, (feedback_id,))
            new_feedback = cursor.fetchone()
            
            # 处理JSON字段，将JSON字符串转换为Python对象
            if new_feedback:
                for field in json_fields:
                    if field in new_feedback and new_feedback[field]:
                        try:
                            new_feedback[field] = json.loads(new_feedback[field])
                        except (json.JSONDecodeError, TypeError):
                            # 如果解析失败，保持原样
                            pass
            
            return {
                "status": "success", 
                "message": "反馈提交成功", 
                "feedback_id": feedback_id,
                "data": new_feedback
            }
    except Exception as e:
        return {"status": "error", "message": f"提交反馈失败: {str(e)}"}
    finally:
        conn.close()



# 查询距离当前位置最近的活动
@app.post("/activities/nearby/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_nearby_activities(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    latitude = data.get("latitude")  # 纬度
    longitude = data.get("longitude")  # 经度
    limit = data.get("limit", 16)  # 默认每页8条
    page = data.get("page", 1)  # 默认第1页
    activityCategory = data.get("Category")  # 活动分类，默认为None表示所有分类
    
    if not latitude or not longitude:
        return {"status": "error", "message": "缺少经纬度信息"}
    
    # 计算偏移量
    offset = (page - 1) * limit
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 构建基础SQL查询
            base_sql = """
            SELECT 
                a.activityId, a.activityTitle, a.activityImages, a.activityType, 
                a.activityTags, a.activityDetails, a.activityLocation, 
                ST_X(a.activityCoordinates) as longitude, 
                ST_Y(a.activityCoordinates) as latitude,
                a.activityPublishTime, a.activityCategory, a.activityRestriction,
                a.activityCurrentParticipants, a.activityMaxParticipants,
                a.publisherId,
                u.userAvatarUrl as publisherAvatar,
                u.username as publisherNickname,
                u.userSignature as publisherSignature,
                u.userTags as publisherTags,
                a.activityStatus,
                a.status, a.extraData,
                ST_Distance_Sphere(
                    a.activityCoordinates, 
                    POINT(%s, %s)
                ) as distance,
                TIMESTAMPDIFF(MINUTE, a.activityPublishTime, NOW()) as minutes_ago,
                (GREATEST(1000, ST_Distance_Sphere(
                    a.activityCoordinates, 
                    POINT(%s, %s)
                )) * GREATEST(1, LEAST(20, TIMESTAMPDIFF(MINUTE, a.created_at, NOW()) * 0.01))) as weight_score
            FROM activities a
            LEFT JOIN users u ON a.publisherId = u.userid
            WHERE NOT JSON_CONTAINS(a.activityStatus, '"删除"')
            AND (
                NOT JSON_CONTAINS_PATH(a.extraData, 'one', '$.activityEndTime') 
                OR STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(a.extraData, '$.activityEndTime')), '%%Y-%%m-%%d %%H:%%i') >= NOW()
            )
            """
            
            params = [longitude, latitude, longitude, latitude]
            
            # 添加分类筛选条件 - 修改为使用JSON_CONTAINS实现任意匹配
            if activityCategory:
                # 确保activityCategory是列表格式
                if isinstance(activityCategory, str):
                    try:
                        activityCategory = json.loads(activityCategory)
                    except json.JSONDecodeError:
                        activityCategory = [activityCategory]
                
                if not isinstance(activityCategory, list):
                    activityCategory = [activityCategory]
                
                # 构建OR条件，查找包含任一分类的活动
                category_conditions = []
                for category in activityCategory:
                    if category:  # 跳过空值
                        # 使用JSON_CONTAINS函数检查数组中是否包含特定值
                        category_conditions.append("JSON_CONTAINS(activityCategory, %s, '$')")
                        params.append(json.dumps(category))
                
                if category_conditions:
                    base_sql += f" AND ({' OR '.join(category_conditions)})"
            
            # 添加排序和分页 - 修改为使用权重分数排序
            base_sql += " ORDER BY weight_score LIMIT %s OFFSET %s"
            params.extend([limit, offset])
            
            # 执行查询
            cursor.execute(base_sql, params)
            activities = cursor.fetchall()
            
            # 查询总记录数（用于分页信息）- 添加相同的过期过滤条件
            count_sql = """
            SELECT COUNT(*) as total FROM activities 
            WHERE NOT JSON_CONTAINS(activityStatus, '"删除"')
            AND (
                NOT JSON_CONTAINS_PATH(extraData, 'one', '$.activityEndTime') 
                OR STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(extraData, '$.activityEndTime')), '%%Y-%%m-%%d %%H:%%i') >= NOW()
            )
            """
            
            count_params = []
            
            # 如果提供了活动分类，添加分类筛选条件到计数查询
            if activityCategory:
                # 构建与上面相同的分类条件
                category_conditions = []
                for category in activityCategory:
                    if category:  # 跳过空值
                        category_conditions.append("JSON_CONTAINS(activityCategory, %s, '$')")
                        count_params.append(json.dumps(category))
                
                if category_conditions:
                    count_sql += f" AND ({' OR '.join(category_conditions)})"
            
            cursor.execute(count_sql, count_params)
            total_count = cursor.fetchone()['total']
            total_pages = (total_count + limit - 1) // limit  # 计算总页数
            
            # 处理JSON字段
            for activity in activities:
                if activity.get('activityImages'):
                    activity['activityImages'] = json.loads(activity['activityImages'])
                if activity.get('activityCategory'):
                    activity['activityCategory'] = json.loads(activity['activityCategory'])
                if activity.get('activityTags'):
                    activity['activityTags'] = json.loads(activity['activityTags'])
                if activity.get('publisherTags'):
                    activity['publisherTags'] = json.loads(activity['publisherTags'])
                if activity.get('extraData'):
                    activity['extraData'] = json.loads(activity['extraData'])
                if activity.get('status'):
                    activity['status'] = json.loads(activity['status'])
                if activity.get('activityRestriction'):
                    activity['activityRestriction'] = json.loads(activity['activityRestriction'])
                if activity.get('activityType'):
                    activity['activityType'] = json.loads(activity['activityType'])
                if activity.get('activityStatus'):
                    activity['activityStatus'] = json.loads(activity['activityStatus'])
                # 将距离转换为公里
                if activity.get('distance'):
                    activity['distance'] = round(activity['distance'] / 1000, 2)  # 转换为公里并保留两位小数
            
            # 返回结果，包含分页信息
            return {
                "status": "success", 
                "data": activities,
                "pagination": {
                    "total": total_count,
                    "page": page,
                    "limit": limit,
                    "total_pages": total_pages
                }
            }
    except Exception as e:
        return {"status": "error", "message": f"查询活动失败: {str(e)}"}
    finally:
        conn.close()



@app.post("/users/daily_activities_comments/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_user_daily_activities_comments(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("userId")
    date_str = data.get("date")
    activity_type = data.get("type", "all")  # 默认查询所有类型
    
    if not user_id:
        return {"status": "error", "message": "请提供用户ID"}
    
    if not date_str:
        return {"status": "error", "message": "请提供日期"}
    
    try:
        # 解析日期字符串为日期对象
        query_date = datetime.strptime(date_str, "%Y-%m-%d").date()
        # 计算日期范围（当天的开始和结束）
        start_datetime = datetime.combine(query_date, datetime.min.time())
        end_datetime = datetime.combine(query_date, datetime.max.time())
        
        conn = get_conn()
        result = {"status": "success", "data": []}
        
        try:
            with conn.cursor(cursors.DictCursor) as cursor:
                # 查询用户在指定日期发布的评论
                comments_sql = """
                SELECT 
                    commentId, commentTitle, commentContent, commentImages, 
                    commentCategory, commentTags, commentLocation,
                    commentPublishTime, publisherId, publisherAvatar, 
                    publisherNickname, likeCount, commentCount, shareCount
                FROM comments
                WHERE publisherId = %s 
                AND commentPublishTime BETWEEN %s AND %s
                AND NOT JSON_CONTAINS(status, '"删除"')
                """
                cursor.execute(comments_sql, (user_id, start_datetime, end_datetime))
                comments = cursor.fetchall()
                
                # 查询用户在指定日期参与的活动
                activities_sql = """
                SELECT 
                    a.activityId, a.activityTitle, a.activityImages, a.activityType,
                    a.activityDetails, a.activityLocation, a.activityPublishTime,
                    a.publisherId, a.publisherAvatar, a.publisherNickname,
                    a.activityCurrentParticipants, a.activityMaxParticipants
                FROM activities a
                WHERE (a.publisherId = %s OR EXISTS (
                    SELECT 1 FROM activity_participants ap 
                    WHERE ap.activityId = a.activityId AND ap.userId = %s
                ))
                AND a.activityPublishTime BETWEEN %s AND %s
                AND NOT JSON_CONTAINS(a.status, '"删除"')
                """
                cursor.execute(activities_sql, (user_id, user_id, start_datetime, end_datetime))
                activities = cursor.fetchall()
                
                # 处理评论数据
                comment_items = []
                for comment in comments:
                    # 处理JSON字段
                    if comment.get('commentImages'):
                        if isinstance(comment['commentImages'], str):
                            try:
                                comment['commentImages'] = json.loads(comment['commentImages'])
                                # 检查是否是带有 values 键的对象
                                if isinstance(comment['commentImages'], dict) and 'values' in comment['commentImages']:
                                    comment['commentImages'] = comment['commentImages']['values']
                            except json.JSONDecodeError:
                                comment['commentImages'] = []
                    
                    if comment.get('commentTags'):
                        if isinstance(comment['commentTags'], str):
                            try:
                                comment['commentTags'] = json.loads(comment['commentTags'])
                            except json.JSONDecodeError:
                                comment['commentTags'] = []
                    
                    if comment.get('commentCategory'):
                        if isinstance(comment['commentCategory'], str):
                            try:
                                comment['commentCategory'] = json.loads(comment['commentCategory'])
                            except json.JSONDecodeError:
                                comment['commentCategory'] = []
                    
                    # 创建统一格式的项目
                    item = {
                        "id": comment.get("commentId"),
                        "title": comment.get("commentTitle", "无标题评论"),
                        "content": comment.get("commentContent", ""),
                        "images": comment.get("commentImages", []),
                        "location": comment.get("commentLocation", ""),
                        "createTime": comment.get("commentPublishTime").strftime("%Y-%m-%d %H:%M:%S") if comment.get("commentPublishTime") else "",
                        "type": "评论",
                        "category": comment.get("commentCategory", []),
                        "tags": comment.get("commentTags", []),
                        "likeCount": comment.get("likeCount", 0),
                        "commentCount": comment.get("commentCount", 0),
                        "publisherId": comment.get("publisherId"),
                        "publisherAvatar": comment.get("publisherAvatar"),
                        "publisherNickname": comment.get("publisherNickname")
                    }
                    comment_items.append(item)
                
                # 处理活动数据
                activity_items = []
                for activity in activities:
                    # 处理JSON字段
                    if activity.get('activityImages'):
                        if isinstance(activity['activityImages'], str):
                            try:
                                activity['activityImages'] = json.loads(activity['activityImages'])
                                # 检查是否是带有 values 键的对象
                                if isinstance(activity['activityImages'], dict) and 'values' in activity['activityImages']:
                                    activity['activityImages'] = activity['activityImages']['values']
                            except json.JSONDecodeError:
                                activity['activityImages'] = []
                    
                    if activity.get('activityType'):
                        if isinstance(activity['activityType'], str):
                            try:
                                activity['activityType'] = json.loads(activity['activityType'])
                            except json.JSONDecodeError:
                                activity['activityType'] = []
                    
                    # 创建统一格式的项目
                    item = {
                        "id": activity.get("activityId"),
                        "title": activity.get("activityTitle", "无标题活动"),
                        "content": activity.get("activityDetails", ""),
                        "images": activity.get("activityImages", []),
                        "location": activity.get("activityLocation", ""),
                        "createTime": activity.get("activityPublishTime").strftime("%Y-%m-%d %H:%M:%S") if activity.get("activityPublishTime") else "",
                        "type": "活动",
                        "category": activity.get("activityType", []),
                        "currentParticipants": activity.get("activityCurrentParticipants", 0),
                        "maxParticipants": activity.get("activityMaxParticipants", 0),
                        "publisherId": activity.get("publisherId"),
                        "publisherAvatar": activity.get("publisherAvatar"),
                        "publisherNickname": activity.get("publisherNickname")
                    }
                    activity_items.append(item)
                
                # 合并评论和活动数据
                all_items = comment_items + activity_items
                
                # 根据类型筛选
                if activity_type != "all":
                    if activity_type == "评论":
                        all_items = comment_items
                    elif activity_type == "活动":
                        all_items = activity_items
                
                # 按时间排序（降序，最新的在前面）
                all_items.sort(key=lambda x: x["createTime"], reverse=True)
                
                # 返回结果
                result["data"] = all_items
                result["total"] = len(all_items)
                result["date"] = date_str
                result["userId"] = user_id
                
                return result
                
        except Exception as e:
            return {"status": "error", "message": f"查询失败: {str(e)}"}
        finally:
            conn.close()
            
    except ValueError:
        return {"status": "error", "message": "日期格式无效，请使用YYYY-MM-DD格式"}





# 查询活动和用户信息的组合路由
@app.post("/query/activity_user/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def query_activity_user(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    activity_id = data.get("activityId")
    # Renaming user_id from request to requesting_user_id for clarity
    # This ID is for the user making the request (viewer), used for like/participation checks
    requesting_user_id = data.get("userId") 
    comment_id = data.get("commentId")  # 评论ID参数
    
    if not activity_id and not requesting_user_id and not comment_id:
        return {"status": "error", "message": "请至少提供活动ID、用户ID或评论ID"}
    
    result = {"status": "success"}
    conn = get_conn()
    
    # Initialize activity and comment to None
    activity_data = None
    comment_data = None

    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 如果提供了活动ID，查询活动信息
            if activity_id:
                activity_sql = """
                SELECT 
                    activityId, activityTitle, activityImages, activityType, 
                    activityTags, activityDetails, activityLocation, 
                    ST_X(activityCoordinates) as longitude, 
                    ST_Y(activityCoordinates) as latitude,
                    activityPublishTime, activityCategory, activityRestriction,
                    activityCurrentParticipants, activityMaxParticipants,
                    publisherId, publisherAvatar, publisherNickname,
                    publisherSignature, publisherTags, activityStatus,
                    status, extraData
                FROM activities
                WHERE activityId = %s
                """
                cursor.execute(activity_sql, (activity_id,))
                activity_data = cursor.fetchone()
                
                if activity_data:
                    # 处理JSON字段
                    if isinstance(activity_data.get('activityImages'), str):
                        try: activity_data['activityImages'] = json.loads(activity_data['activityImages'])
                        except json.JSONDecodeError: pass # Keep as is or handle error
                    if isinstance(activity_data.get('activityTags'), str):
                        try: activity_data['activityTags'] = json.loads(activity_data['activityTags'])
                        except json.JSONDecodeError: pass
                    if isinstance(activity_data.get('publisherTags'), str): # This is from activities table, might be outdated
                        try: activity_data['publisherTags'] = json.loads(activity_data['publisherTags'])
                        except json.JSONDecodeError: pass
                    if isinstance(activity_data.get('extraData'), str):
                        try: activity_data['extraData'] = json.loads(activity_data['extraData'])
                        except json.JSONDecodeError: pass
                    if isinstance(activity_data.get('activityRestriction'), str):
                        try: activity_data['activityRestriction'] = json.loads(activity_data['activityRestriction'])
                        except json.JSONDecodeError: pass
                    if isinstance(activity_data.get('activityType'), str):
                        try: activity_data['activityType'] = json.loads(activity_data['activityType'])
                        except json.JSONDecodeError: pass
                    
                    result["activity"] = activity_data
                else:
                    result["activity"] = None
                    result["activity_message"] = "活动不存在或已关闭"
            
            # 新增：如果提供了评论ID，查询评论信息
            if comment_id:
                comment_sql = """
                SELECT 
                    c.commentId, c.commentTitle, c.commentContent, c.commentImages, 
                    ST_X(c.commentCoordinates) as longitude,
                    ST_Y(c.commentCoordinates) as latitude,
                    c.commentCategory, c.commentTags, c.commentLocation,
                    c.commentPublishTime, c.shoppingImages,
                    c.publisherId, c.publisherAvatar, c.publisherNickname,
                    c.publisherSignature, c.publisherTags, c.publisherPoints,
                    c.likeCount, c.commentCount, c.shareCount, 
                    c.status, c.extraData, c.created_at, c.updated_at,
                    IFNULL(cl.status, 0) as is_liked
                FROM comments c
                LEFT JOIN comment_likes cl ON c.commentId = cl.commentId AND cl.userId = %s
                WHERE c.commentId = %s
                """
                cursor.execute(comment_sql, (requesting_user_id if requesting_user_id else 0, comment_id))
                comment_data = cursor.fetchone()
                
                if comment_data:
                    # 处理JSON字段
                    if isinstance(comment_data.get('commentImages'), str):
                        try: comment_data['commentImages'] = json.loads(comment_data['commentImages'])
                        except json.JSONDecodeError: pass
                    if isinstance(comment_data.get('commentTags'), str):
                        try: comment_data['commentTags'] = json.loads(comment_data['commentTags'])
                        except json.JSONDecodeError: pass
                    if isinstance(comment_data.get('shoppingImages'), str):
                        try: comment_data['shoppingImages'] = json.loads(comment_data['shoppingImages'])
                        except json.JSONDecodeError: pass
                    if isinstance(comment_data.get('publisherTags'), str): # This is from comments table
                        try: comment_data['publisherTags'] = json.loads(comment_data['publisherTags'])
                        except json.JSONDecodeError: pass
                    if isinstance(comment_data.get('extraData'), str):
                        try: comment_data['extraData'] = json.loads(comment_data['extraData'])
                        except json.JSONDecodeError: pass
                    
                    # 查询评论的回复
                    replies_sql = """
                    SELECT 
                        replyId, commentId, replyContent, replyImages,
                        publisherId, publisherAvatar, publisherNickname,
                        replyTime, likeCount, parentReplyId, status, extraData
                    FROM comment_replies
                    WHERE commentId = %s
                    ORDER BY 
                        CASE WHEN parentReplyId IS NULL THEN replyId ELSE parentReplyId END,
                        replyTime
                    """
                    cursor.execute(replies_sql, (comment_id,))
                    replies = cursor.fetchall()
                    
                    for reply in replies:
                        if isinstance(reply.get('replyImages'), str):
                            try: reply['replyImages'] = json.loads(reply['replyImages'])
                            except json.JSONDecodeError: pass
                        if isinstance(reply.get('extraData'), str):
                            try: reply['extraData'] = json.loads(reply['extraData'])
                            except json.JSONDecodeError: pass
                        if reply.get('replyTime'):
                            reply['replyTime'] = reply['replyTime'].strftime("%Y-%m-%d %H:%M:%S")
                    
                    comment_data['replies'] = replies
                    result["comment"] = comment_data
                else:
                    result["comment"] = None
                    result["comment_message"] = "评论不存在或已删除"

            # --- MODIFIED SECTION: Fetch publisher's user details and update old data ---
            publisher_to_fetch_id = None
            source_entity_message = ""

            if activity_data and activity_data.get('publisherId'):
                publisher_to_fetch_id = activity_data.get('publisherId')
                source_entity_message = "activity"
            elif comment_data and comment_data.get('publisherId'):
                # If no activity_data, or activity_data had no publisherId, use comment's publisher
                publisher_to_fetch_id = comment_data.get('publisherId')
                source_entity_message = "comment"

            if publisher_to_fetch_id:
                user_sql = "SELECT * FROM users WHERE userid = %s"
                cursor.execute(user_sql, (publisher_to_fetch_id,))
                publisher_user_data = cursor.fetchone()
                if publisher_user_data:
                    # Process JSON fields for the publisher's user data
                    json_fields_for_user = ['userTags', 'userHobbies', 'userImgUrls', 'userData', 'extraData']
                    for field in json_fields_for_user:
                        field_value = publisher_user_data.get(field)
                        if isinstance(field_value, str):
                            try:
                                publisher_user_data[field] = json.loads(field_value)
                            except json.JSONDecodeError:
                                # Keep as is or set to default if parsing fails
                                publisher_user_data[field] = None # Or [], {} based on expected type
                    
                    result["user"] = publisher_user_data  # This now contains the publisher's info
                    
                    # 用最新的用户数据替换activity中的旧publisher数据
                    if activity_data and activity_data.get('publisherId') == publisher_to_fetch_id:
                        activity_data['publisherAvatar'] = publisher_user_data.get('userAvatarUrl', '')
                        activity_data['publisherNickname'] = publisher_user_data.get('username', '')
                        activity_data['publisherSignature'] = publisher_user_data.get('userSignature', '')
                        activity_data['publisherTags'] = publisher_user_data.get('userTags', [])
                        result["activity"] = activity_data
                    
                    # 用最新的用户数据替换comment中的旧publisher数据
                    if comment_data and comment_data.get('publisherId') == publisher_to_fetch_id:
                        comment_data['publisherAvatar'] = publisher_user_data.get('userAvatarUrl', '')
                        comment_data['publisherNickname'] = publisher_user_data.get('username', '')
                        comment_data['publisherSignature'] = publisher_user_data.get('userSignature', '')
                        comment_data['publisherTags'] = publisher_user_data.get('userTags', [])
                        comment_data['publisherPoints'] = publisher_user_data.get('userPoints', 0)
                        result["comment"] = comment_data
                    
                    # 同样更新回复中的publisher数据
                    if comment_data and comment_data.get('replies'):
                        for reply in comment_data['replies']:
                            if reply.get('publisherId') == publisher_to_fetch_id:
                                reply['publisherAvatar'] = publisher_user_data.get('userAvatarUrl', '')
                                reply['publisherNickname'] = publisher_user_data.get('username', '')
                                
                        result["comment"] = comment_data
                        
                else:
                    result["user"] = None
                    result["user_message"] = f"Publisher (ID: {publisher_to_fetch_id}) for the {source_entity_message} not found."
            else:
                # 中文：没有发布者信息，因为活动或评论没有发布者ID
                result["user"] = None
                if activity_id or comment_id: 
                    result["user_message"] = "Could not determine publisher from the provided activity/comment ID, or entity not found/has no publisher."
                # 中文：如果没有提供活动ID和评论ID，result["user"]将保持为None，无需特定消息
            # 中文：结束
            # 如果活动ID和用户ID都提供了，检查用户是否参与了该活动
            if activity_id and requesting_user_id and result.get("activity"):
                participation_sql = """
                SELECT * FROM activity_participants 
                WHERE activityId = %s AND userId = %s
                """
                cursor.execute(participation_sql, (activity_id, requesting_user_id))
                participation = cursor.fetchone()
                
                if participation:
                    if isinstance(participation.get('extraData'), str):
                        try: participation['extraData'] = json.loads(participation['extraData'])
                        except json.JSONDecodeError: pass
                    result["participation"] = {
                        "activityId": participation["activityId"],
                        "userId": participation["userId"],
                        "participantStatus": participation["participantStatus"],
                        "joinTime": participation["joinTime"].strftime("%Y-%m-%d %H:%M:%S") if participation["joinTime"] else None,
                        "extraData": participation.get("extraData"),
                        "status": participation.get("status")
                    }
                    result["is_participant"] = True
                else:
                    result["is_participant"] = False
                    result["participation"] = None
            
            # 如果三个ID都提供了，可以进行更复杂的关联检查
            if activity_id and requesting_user_id and comment_id and result.get("activity") and result.get("user") and result.get("comment"):
                # 检查评论是否与活动相关联（如果有相关字段）
                # This example assumes a 'relatedActivityId' field in comments, adjust if necessary
                # if result["comment"].get("relatedActivityId") == activity_id:
                #     result["comment_related_to_activity"] = True
                # else:
                #     result["comment_related_to_activity"] = False
                pass # Placeholder for complex association logic if needed
            
            return result
    except Exception as e:
        return {"status": "error", "message": f"查询失败: {str(e)}"}
    finally:
        conn.close()

# 根据店铺名查询评论的回复
# 根据店铺名查询评论的回复
@app.post("/query/comments_by_store/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def query_comments_by_store(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    store_name = data.get("store_name")
    request_user_id_str = data.get("userId")  # 可选参数

    if not store_name:
        return {"status": "error", "message": "请提供店铺名称"}

    result = {"status": "success"}
    conn = get_conn()
    
    request_user_id = None
    if request_user_id_str is not None:
        try:
            request_user_id = int(request_user_id_str)
        except ValueError:
            conn.close()
            return {"status": "error", "message": "无效的用户ID格式"}

    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 初始化店铺交互信息
            shop_interaction_details = {
                "shopId": None,
                "isCheckedIn": False,
                "hasLikedShop": False,
                "message": "用户未登录或店铺信息未查询"
            }

            if request_user_id is not None:
                # 查询店铺信息以获取 shopId 及用户打卡、点赞状态
                shop_query_sql = """
                SELECT shopId, shopCheckInIds, shopLikeIds
                FROM shops
                WHERE shopName = %s
                """
                cursor.execute(shop_query_sql, (store_name,))
                shop_data = cursor.fetchone()

                if shop_data:
                    shop_interaction_details["shopId"] = shop_data['shopId']
                    
                    shop_check_in_ids_json = shop_data.get('shopCheckInIds')
                    shop_check_in_ids = []
                    if shop_check_in_ids_json:
                        try:
                            # 尝试解析，如果已经是列表则直接使用
                            loaded_ids = json.loads(shop_check_in_ids_json)
                            if isinstance(loaded_ids, list):
                                shop_check_in_ids = loaded_ids
                        except json.JSONDecodeError:
                            # 处理解析失败的情况，例如记录日志
                            pass # shop_check_in_ids 保持为空列表

                    shop_like_ids_json = shop_data.get('shopLikeIds')
                    shop_like_ids = []
                    if shop_like_ids_json:
                        try:
                            loaded_ids = json.loads(shop_like_ids_json)
                            if isinstance(loaded_ids, list):
                                shop_like_ids = loaded_ids
                        except json.JSONDecodeError:
                            pass # shop_like_ids 保持为空列表
                    
                    # 确保 request_user_id 和列表中的ID类型一致进行比较
                    # 假设 shop_check_in_ids 和 shop_like_ids 存储的是整数用户ID
                    if request_user_id in shop_check_in_ids:
                        shop_interaction_details["isCheckedIn"] = True
                    
                    if request_user_id in shop_like_ids:
                        shop_interaction_details["hasLikedShop"] = True
                    shop_interaction_details["message"] = "店铺交互状态已查询"
                else:
                    shop_interaction_details["message"] = f"未找到店铺名为 '{store_name}' 的店铺信息"
            
            result["shopInteraction"] = shop_interaction_details

            # 构建查询评论的SQL - 添加点赞数和点踩数统计
            comments_sql = """
            SELECT 
                c.commentId, c.commentTitle, c.commentContent, c.commentImages, 
                c.commentCategory, c.commentTags, c.commentLocation,
                ST_X(c.commentCoordinates) as longitude, 
                ST_Y(c.commentCoordinates) as latitude,
                c.commentPublishTime, c.shoppingImages,
                c.publisherId, 
                u.username AS publisherUsername,
                u.userAvatarUrl AS publisherUserAvatarUrl,
                u.userSignature AS publisherUserSignature,
                u.userTags AS publisherUserTags,
                u.userPoints AS publisherUserPoints,
                u.extraData AS publisherExtraData,
                c.likeCount, c.commentCount, c.shareCount,
                c.status AS commentStatus, c.extraData, c.created_at, c.updated_at,
                IFNULL(cl.status, 0) as is_liked,
                -- 统计点赞数(status=1)
                (SELECT COUNT(*) FROM comment_likes WHERE commentId = c.commentId AND status = 1) AS like_count,
                -- 统计点踩数(status=2)
                (SELECT COUNT(*) FROM comment_likes WHERE commentId = c.commentId AND status = 2) AS dislike_count
            FROM comments c
            LEFT JOIN users u ON c.publisherId = u.userid
            LEFT JOIN comment_likes cl ON c.commentId = cl.commentId AND cl.userId = %s
            WHERE JSON_CONTAINS(c.extraData, JSON_OBJECT('Store_name', %s))
            ORDER BY c.commentPublishTime DESC
            """

            cursor.execute(comments_sql, (request_user_id if request_user_id is not None else 0, store_name))
            comments = cursor.fetchall()

            if comments:
                for comment in comments:
                    # ... existing code ...
                    # 1. 处理从users表获取的发布者信息
                    publisher_user_tags_str = comment.pop('publisherUserTags', None)
                    publisher_extra_data_str = comment.pop('publisherExtraData', None)  # 获取extraData
                    
                    comment['publisherInfo'] = {
                        'userid': comment['publisherId'], # publisherId is already in comment
                        'username': comment.pop('publisherUsername', None),
                        'userAvatarUrl': comment.pop('publisherUserAvatarUrl', None),
                        'userSignature': comment.pop('publisherUserSignature', None),
                        'userTags': json.loads(publisher_user_tags_str) if publisher_user_tags_str and isinstance(publisher_user_tags_str, str) else [],
                        'userPoints': comment.pop('publisherUserPoints', None),
                        'extraData': json.loads(publisher_extra_data_str) if publisher_extra_data_str and isinstance(publisher_extra_data_str, str) else {}  # 解析extraData为JSON
                    }

                    # 添加明确的点赞统计数据
                    comment['reaction_stats'] = {
                        'likes': comment.pop('like_count', 0),    # 点赞数
                        'dislikes': comment.pop('dislike_count', 0)  # 点踩数
                    }

                    # 2. 如果提供了userId，查询与评论发布者的好友关系
                    comment['friendshipWithUser'] = {"relationship": "not_queried"} # Default
                    publisher_id = comment['publisherId']

                    if request_user_id is not None and publisher_id is not None:
                        if request_user_id == publisher_id:
                            comment['friendshipWithUser'] = {"relationship": "self"}
                        else:
                            # 检查是否是好友
                            check_friendship_sql = """
                            SELECT friendship_id, user1_id, user2_id, status, remark, group_id, intimacy_score, last_interaction, created_at 
                            FROM friendships 
                            WHERE (user1_id = %s AND user2_id = %s) OR (user1_id = %s AND user2_id = %s)
                            """
                            cursor.execute(check_friendship_sql, (request_user_id, publisher_id, publisher_id, request_user_id))
                            friendship = cursor.fetchone()
                            
                            if friendship:
                                status_map = {1: "正常好友", 2: "特别关注", 3: "黑名单"}
                                status_text = status_map.get(friendship["status"], "未知状态")
                                friendship_details = {
                                    "friendship_id": friendship["friendship_id"],
                                    "status": friendship["status"],
                                    "status_text": status_text,
                                    "remark": friendship["remark"],
                                    "group_id": friendship["group_id"],
                                    "intimacy_score": friendship["intimacy_score"],
                                    "last_interaction": friendship["last_interaction"].strftime("%Y-%m-%d %H:%M:%S") if friendship["last_interaction"] else None,
                                    "created_at": friendship["created_at"].strftime("%Y-%m-%d %H:%M:%S") if friendship["created_at"] else None
                                }
                                comment['friendshipWithUser'] = {
                                    "relationship": "friends",
                                    "details": friendship_details
                                }
                            else:
                                # 检查是否有待处理的好友申请
                                check_request_sql = """
                                SELECT request_id, sender_id, receiver_id, message, status as request_status, created_at, expired_at 
                                FROM friend_requests 
                                WHERE ((sender_id = %s AND receiver_id = %s) OR (sender_id = %s AND receiver_id = %s))
                                AND status = 1
                                """ # status = 1 means pending
                                cursor.execute(check_request_sql, (request_user_id, publisher_id, publisher_id, request_user_id))
                                request_record = cursor.fetchone()
                                
                                if request_record:
                                    direction = "sent" if request_record["sender_id"] == request_user_id else "received"
                                    request_details = {
                                        "request_id": request_record["request_id"],
                                        "message": request_record["message"],
                                        "created_at": request_record["created_at"].strftime("%Y-%m-%d %H:%M:%S") if request_record["created_at"] else None,
                                        "expired_at": request_record["expired_at"].strftime("%Y-%m-%d %H:%M:%S") if request_record["expired_at"] else None
                                    }
                                    comment['friendshipWithUser'] = {
                                        "relationship": "pending",
                                        "direction": direction,
                                        "details": request_details
                                    }
                                else:
                                    comment['friendshipWithUser'] = {"relationship": "none"}
                    
                    # 处理原有的JSON和时间格式化
                    if comment.get('commentImages') and isinstance(comment['commentImages'], str):
                        comment['commentImages'] = json.loads(comment['commentImages'])
                    if comment.get('commentTags') and isinstance(comment['commentTags'], str):
                        comment['commentTags'] = json.loads(comment['commentTags'])
                    if comment.get('commentCategory') and isinstance(comment['commentCategory'], str):
                        comment['commentCategory'] = json.loads(comment['commentCategory'])
                    if comment.get('shoppingImages') and isinstance(comment['shoppingImages'], str):
                        comment['shoppingImages'] = json.loads(comment['shoppingImages'])
                    
                    if comment.get('commentStatus') and isinstance(comment['commentStatus'], str): 
                        comment['status'] = json.loads(comment['commentStatus']) 
                    elif 'commentStatus' in comment and comment['commentStatus'] is None: # explicitly check for None if column can be NULL
                        comment['status'] = None
                    # If 'commentStatus' key might not exist or you want to default to None
                    elif 'commentStatus' not in comment :
                         comment['status'] = None

                    if comment.get('extraData') and isinstance(comment['extraData'], str):
                        comment['extraData'] = json.loads(comment['extraData'])
                    
                    if comment.get('commentPublishTime') and hasattr(comment['commentPublishTime'], 'strftime'):
                        comment['commentPublishTime'] = comment['commentPublishTime'].strftime("%Y-%m-%d %H:%M:%S")
                    if comment.get('created_at') and hasattr(comment['created_at'], 'strftime'):
                        comment['created_at'] = comment['created_at'].strftime("%Y-%m-%d %H:%M:%S")
                    if comment.get('updated_at') and hasattr(comment['updated_at'], 'strftime'):
                        comment['updated_at'] = comment['updated_at'].strftime("%Y-%m-%d %H:%M:%S")
                # ... existing code ...
                result["comments"] = comments
                result["total"] = len(comments)
            else:
                result["comments"] = []
                result["total"] = 0
                result["message"] = f"未找到店铺名为 '{store_name}' 的评论"
            
            return result
    except Exception as e:
        if conn: conn.rollback() # Rollback on error
        return {"status": "error", "message": f"查询失败: {str(e)}"}
    finally:
        if conn: conn.close()



@app.post("/shop/check-in/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def toggle_shop_check_in(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    shop_id = data.get("shopId")
    user_id = data.get("userId")
    # 获取用户位置信息
    user_longitude = data.get("longitude")
    user_latitude = data.get("latitude")

    if shop_id is None or user_id is None:
        return {"status": "error", "message": "shopId 和 userId 不能为空"}

    try:
        shop_id = int(shop_id)
        user_id = int(user_id)
        # 尝试转换经纬度为浮点数
        if user_longitude is not None and user_latitude is not None:
            user_longitude = float(user_longitude)
            user_latitude = float(user_latitude)
    except ValueError:
        return {"status": "error", "message": "参数格式不正确，请确保shopId、userId为整数，经纬度为数值"}

    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 首先检查距离（如果提供了位置信息）
            if user_longitude is not None and user_latitude is not None:
                # 查询店铺坐标并计算距离
                location_check_sql = """
                SELECT 
                    shopId, 
                    ST_Distance_Sphere(
                        shopCoordinates, 
                        POINT(%s, %s)
                    ) as distance
                FROM shops 
                WHERE shopId = %s
                """
                cursor.execute(location_check_sql, (user_longitude, user_latitude, shop_id))
                location_result = cursor.fetchone()
                
                if location_result:
                    # 如果店铺有坐标信息，检查距离
                    distance = location_result.get('distance')
                    if distance is not None:  # 如果有距离数据（店铺有坐标）
                        if distance > 500:  # 距离超过500米
                            return {
                                "status": "error", 
                                "message": f"距离太远，无法打卡。您当前距离店铺{round(distance/1000, 2)}公里，需要在500米范围内。"
                            }
            
            conn.begin() # 开始事务

            # 查询店铺当前的打卡信息，并锁定行以防止并发问题
            select_sql = "SELECT shopCheckInIds, shopCheckInCount FROM shops WHERE shopId = %s FOR UPDATE"
            cursor.execute(select_sql, (shop_id,))
            shop_info = cursor.fetchone()

            if not shop_info:
                conn.rollback()
                return {"status": "error", "message": f"未找到 shopId 为 {shop_id} 的店铺"}

            current_check_in_ids_json = shop_info.get('shopCheckInIds')
            current_check_in_ids = []
            if current_check_in_ids_json:
                try:
                    loaded_ids = json.loads(current_check_in_ids_json)
                    if isinstance(loaded_ids, list):
                       current_check_in_ids = loaded_ids
                except json.JSONDecodeError:
                    # 如果JSON无效，视为空列表处理，或者可以报错
                    pass 
            
            current_count = shop_info.get('shopCheckInCount', 0)
            if not isinstance(current_count, int): #确保是整数
                current_count = 0

            message = ""
            action_taken = ""

            if user_id in current_check_in_ids:
                # 用户已打卡，执行取消打卡操作
                current_check_in_ids.remove(user_id)
                new_count = max(0, current_count - 1) # 确保不为负
                message = "取消打卡成功"
                action_taken = "undone"
            else:
                # 用户未打卡，执行打卡操作
                current_check_in_ids.append(user_id)
                new_count = current_count + 1
                message = "打卡成功"
                action_taken = "done"
            
            # 更新数据库
            updated_check_in_ids_json = json.dumps(current_check_in_ids)
            update_sql = "UPDATE shops SET shopCheckInIds = %s, shopCheckInCount = %s WHERE shopId = %s"
            cursor.execute(update_sql, (updated_check_in_ids_json, new_count, shop_id))
            
            conn.commit() # 提交事务

            return {
                "status": "success", 
                "message": message, 
                "action": action_taken,
                "shopId": shop_id, 
                "userId": user_id, 
                "newCheckInCount": new_count
            }

    except pymysql.MySQLError as db_err:
        if conn: conn.rollback()
        return {"status": "error", "message": f"数据库操作失败: {str(db_err)}"}
    except Exception as e:
        if conn: conn.rollback()
        return {"status": "error", "message": f"处理打卡操作失败: {str(e)}"}
    finally:
        if conn: conn.close()




# 添加活动
# 发布活动
# 新增活动
@app.post("/activities/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def add_activity(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 首先检查用户积分是否足够
            publisher_id = data.get("publisherId")
            if not publisher_id:
                return {"status": "error", "message": "缺少发布者ID"}
            
            # 获取free参数，默认为0
            # print(f"data: {data}")
            free = data.get("free", 0)
            # print(f"free: {free}")
            # 查询用户当前积分
            check_points_sql = "SELECT userPoints FROM users WHERE userid = %s"
            cursor.execute(check_points_sql, (publisher_id,))
            user = cursor.fetchone()
            
            if not user:
                return {"status": "error", "message": "发布者不存在"}
            
            current_points = user[0]
            # 根据free参数决定是否扣除积分
            points_cost = 0 if free == 1 else 10  # 如果free=1则不扣除积分
            # print(f"current_points: {current_points}, points_cost: {points_cost}")
            if current_points < points_cost:
                # 根据所需积分给出不同的提示信息
                if points_cost > 0:
                    return {"status": "error", "message": f"您当前积分不足，发布活动需要{points_cost}积分"}
                else:
                    return {"status": "error", "message": "您当前积分状态异常，无法发布活动"}
            
            # 准备默认值
            default_values = {
                "activityImages": '[]',
                "activityType": '["AA"]',
                "activityTags": '[]',
                "activityDetails": "",
                "activityLocation": "",
                "activityCategory": '[]',
                "activityRestriction": '[]',
                "activityCurrentParticipants": 0,
                "activityMaxParticipants": 10,
                "publisherAvatar": "",
                "publisherSignature": "",
                "publisherTags": '[]',
                "activityStatus": '["进行中"]',
                "status": '["开启"]',
                "extraData": '{}',
                "activityPublishTime": None  # 添加活动时间的默认值
            }
            
            for key, value in default_values.items():
                if key not in data or data[key] is None:
                    data[key] = value
            
            required_fields = ["activityTitle", "publisherId", "publisherNickname", "activityPublishTime"]  # 添加活动时间为必填字段
            for field in required_fields:
                if field not in data or not data[field]:
                    return {"status": "error", "message": f"缺少必填字段: {field}"}
            
            longitude = data.get("longitude")
            latitude = data.get("latitude")
            if not longitude or not latitude:
                return {"status": "error", "message": "缺少经纬度信息"}
            
            conn.begin()
            
            try:
                sql = """
                INSERT INTO activities (
                    activityTitle, activityImages, activityType, activityTags, 
                    activityDetails, activityLocation, activityCoordinates, 
                    activityCategory, activityRestriction, activityCurrentParticipants,
                    activityMaxParticipants, publisherId, publisherAvatar, 
                    publisherNickname, publisherSignature, publisherTags, 
                    activityStatus, status, extraData, activityPublishTime
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, ST_GeomFromText(%s), %s, %s, %s, 
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                )
                """
                
                for field in ["activityImages", "activityType", "activityTags", "activityCategory", 
                             "activityRestriction", "publisherTags", "activityStatus", "status", "extraData"]:
                    if isinstance(data.get(field), (dict, list)):
                        data[field] = json.dumps(data[field])
                    elif data.get(field) is None:
                        data[field] = '[]' if field in ["activityImages", "activityTags", "publisherTags", "activityCategory", "activityRestriction"] else '{}'
                
                point = f"POINT({longitude} {latitude})"
                params = (
                    data["activityTitle"], 
                    data["activityImages"], 
                    data["activityType"], 
                    data["activityTags"],
                    data["activityDetails"], 
                    data["activityLocation"], 
                    point,
                    data["activityCategory"], 
                    data["activityRestriction"], 
                    data["activityCurrentParticipants"],
                    data["activityMaxParticipants"], 
                    data["publisherId"], 
                    data["publisherAvatar"],
                    data["publisherNickname"], 
                    data["publisherSignature"], 
                    data["publisherTags"],
                    data["activityStatus"], 
                    data["status"], 
                    data["extraData"],
                    data["activityPublishTime"]  # 添加活动时间
                )
                
                cursor.execute(sql, params)
                activity_id = cursor.lastrowid
                
                update_points_sql = "UPDATE users SET userPoints = userPoints - %s WHERE userid = %s"
                cursor.execute(update_points_sql, (points_cost, publisher_id))
                
                points_log_sql = """
                INSERT INTO user_points_log (
                    userid, points_change, points_before, points_after, 
                    change_type, change_reason, related_id, operator, status
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s
                )
                """
                points_log_params = (
                    publisher_id,
                    -points_cost,
                    current_points,
                    current_points - points_cost,
                    6,  # 变动类型：6-其他
                    "发布活动",
                    str(activity_id),
                    "system",
                    "有效" # 显式设置status
                )
                cursor.execute(points_log_sql, points_log_params)
                
                conn.commit()
                if points_cost > 0:
                    message = f"活动添加成功！已扣除{points_cost}积分"
                else:
                    message = "活动添加成功！本次免费发布"
                return {
                    "status": "success", 
                    "message": message, 
                    "activity_id": activity_id,
                    "pointsChange": -points_cost # 返回积分变动信息
                }
            except Exception as e:
                conn.rollback()
                # raise e # 可以选择重新抛出异常或返回更友好的错误信息
                return {"status": "error", "message": f"活动添加事务处理失败: {str(e)}"}
    except Exception as e:
        # 此处的conn.rollback()可能不需要，因为如果事务未开始或已回滚/提交，它可能无效或引发错误
        # 主要的事务回滚已在内部的try-except中处理
        return {"status": "error", "message": f"添加活动失败: {str(e)}"}
    finally:
        if conn: # 确保conn存在才关闭
            conn.close()


# 查询活动
# 返回活动
# 根据活动ID查询活动详情（POST方法）
@app.post("/activities/detail/")
async def get_activity_by_id_post(request: Request):
    data = await request.json()
    activity_id = data.get("activityId")
    
    if not activity_id:
        return {"status": "error", "message": "缺少活动ID"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询活动详情
            sql = """
            SELECT 
                activityId, activityTitle, activityImages, activityType, 
                activityTags, activityDetails, activityLocation, 
                ST_X(activityCoordinates) as longitude, 
                ST_Y(activityCoordinates) as latitude,
                activityPublishTime, activityCategory, activityRestriction,
                activityCurrentParticipants, activityMaxParticipants,
                publisherId, 
                -- publisherAvatar, publisherNickname, publisherSignature, publisherTags, -- 这些字段将从users表获取
                activityStatus,
                status, extraData
            FROM activities
            WHERE activityId = %s
            """
            cursor.execute(sql, (activity_id,))
            activity = cursor.fetchone()
            
            if not activity:
                return {"status": "error", "message": "活动不存在或已关闭"}
            
            # 处理JSON字段
            if activity.get('activityImages'):
                activity['activityImages'] = json.loads(activity['activityImages'])
            if activity.get('activityTags'):
                activity['activityTags'] = json.loads(activity['activityTags'])
            # publisherTags 将从 users 表获取，所以这里不再处理 activities 表中的 publisherTags
            # if activity.get('publisherTags'):
            #     activity['publisherTags'] = json.loads(activity['publisherTags'])
            if activity.get('extraData'):
                activity['extraData'] = json.loads(activity['extraData'])
            if activity.get('activityType'):
                activity['activityType'] = json.loads(activity['activityType'])
            if activity.get('activityCategory'):
                activity['activityCategory'] = json.loads(activity['activityCategory'])
            if activity.get('activityRestriction'):
                activity['activityRestriction'] = json.loads(activity['activityRestriction'])
            if activity.get('activityStatus'):
                activity['activityStatus'] = json.loads(activity['activityStatus'])
            if activity.get('status'): # 活动本身的status
                activity['status'] = json.loads(activity['status'])

            # 根据 publisherId 查询最新的发布者信息
            publisher_id = activity.get("publisherId")
            if publisher_id:
                user_sql = """
                SELECT 
                    userid, username, userAvatarUrl, userSignature, 
                    userTags, userPoints, userLevel, userCity, userGender
                FROM users 
                WHERE userid = %s
                """
                cursor.execute(user_sql, (publisher_id,))
                publisher_info = cursor.fetchone()
                if publisher_info:
                    activity['publisherInfo'] = {
                        'userid': publisher_info['userid'],
                        'username': publisher_info['username'],
                        'userAvatarUrl': publisher_info['userAvatarUrl'],
                        'userSignature': publisher_info['userSignature'],
                        'userTags': json.loads(publisher_info['userTags']) if publisher_info['userTags'] else [],
                        'userPoints': publisher_info['userPoints'],
                        'userLevel': publisher_info['userLevel'],
                        'userCity': publisher_info['userCity'],
                        'userGender': publisher_info['userGender']
                    }
                    # 更新活动中的发布者信息字段，如果活动表本身也存储了这些，可以选择覆盖或移除
                    activity['publisherAvatar'] = publisher_info['userAvatarUrl']
                    activity['publisherNickname'] = publisher_info['username']
                    activity['publisherSignature'] = publisher_info['userSignature']
                    activity['publisherTags'] = json.loads(publisher_info['userTags']) if publisher_info['userTags'] else []
                else:
                    activity['publisherInfo'] = None # 或者一个默认的发布者信息结构
                    # 如果users表中没有找到，保留activities表中的旧数据或设为None
                    activity['publisherAvatar'] = activity.get('publisherAvatar') # 假设activities表中有这些字段
                    activity['publisherNickname'] = activity.get('publisherNickname')
                    activity['publisherSignature'] = activity.get('publisherSignature')
                    activity['publisherTags'] = activity.get('publisherTags') # 之前已处理过json.loads
            else:
                activity['publisherInfo'] = None
                # 如果没有publisherId，也确保这些字段存在，即使为None
                activity['publisherAvatar'] = None
                activity['publisherNickname'] = None
                activity['publisherSignature'] = None
                activity['publisherTags'] = []

            # 查询活动参与者数量
            count_sql = """
            SELECT COUNT(*) as participant_count
            FROM activity_participants
            WHERE activityId = %s
            """
            cursor.execute(count_sql, (activity_id,))
            count_result = cursor.fetchone()
            activity['participant_count'] = count_result['participant_count'] if count_result else 0
            
            return {"status": "success", "data": activity}
    except Exception as e:
        return {"status": "error", "message": f"查询活动详情失败: {str(e)}"}
    finally:
        conn.close()




# 修改活动
@app.post("/activities/edit")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def update_activity(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    activity_id = data.get("activityId")
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 先检查活动是否存在
            check_sql = "SELECT * FROM activities WHERE activityId = %s"
            cursor.execute(check_sql, (activity_id,))
            activity = cursor.fetchone()
            
            if not activity:
                return {"status": "error", "message": "活动不存在"}
            
            # 构建更新语句
            update_fields = []
            params = []
            
            # 处理普通字段
            field_mapping = {
                "activityTitle": "activityTitle",
                "activityDetails": "activityDetails",
                "activityLocation": "activityLocation",
                "activityCurrentParticipants": "activityCurrentParticipants",
                "activityMaxParticipants": "activityMaxParticipants",
                "publisherId": "publisherId",
                "publisherAvatar": "publisherAvatar",
                "publisherNickname": "publisherNickname",
                "publisherSignature": "publisherSignature"
            }
            
            # 处理JSON字段
            json_fields = {
                "activityImages": "activityImages",
                "activityType": "activityType",
                "activityTags": "activityTags",
                "activityCategory": "activityCategory",
                "activityRestriction": "activityRestriction",
                "publisherTags": "publisherTags",
                "activityStatus": "activityStatus",
                "status": "status",
                "extraData": "extraData"
            }
            
            # 处理普通字段
            for client_field, db_field in field_mapping.items():
                if client_field in data:
                    update_fields.append(f"{db_field} = %s")
                    params.append(data[client_field])
            
            # 处理JSON字段 - 确保正确转义
            for client_field, db_field in json_fields.items():
                if client_field in data:
                    update_fields.append(f"{db_field} = %s")
                    # 如果已经是字符串，尝试解析确保是有效JSON，然后再转回字符串
                    if isinstance(data[client_field], str):
                        try:
                            json_value = json.loads(data[client_field])
                            params.append(json.dumps(json_value, ensure_ascii=False))
                        except json.JSONDecodeError:
                            # 如果不是有效的JSON字符串，则按原样处理
                            params.append(data[client_field])
                    else:
                        # 如果是Python对象（如字典或列表），直接转为JSON字符串
                        params.append(json.dumps(data[client_field], ensure_ascii=False))
            
            # 处理坐标
            if "longitude" in data and "latitude" in data:
                longitude = data["longitude"]
                latitude = data["latitude"]
                update_fields.append("activityCoordinates = ST_GeomFromText(%s)")
                params.append(f"POINT({longitude} {latitude})")
            
            if not update_fields:
                return {"status": "error", "message": "没有提供要更新的字段"}
            
            # 构建完整的SQL语句
            params.append(activity_id)  # 添加WHERE条件的参数
            update_sql = f"UPDATE activities SET {', '.join(update_fields)} WHERE activityId = %s"
            
            cursor.execute(update_sql, params)
            conn.commit()
            
            return {"status": "success", "message": "活动更新成功"}
    except Exception as e:
        return {"status": "error", "message": f"更新活动失败: {str(e)}"}
    finally:
        conn.close()



# 查询活动参与者
# 获取活动参与者列表（仅返回审核通过的参与者）
@app.post("/activities/participants/edit")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_activity_participants(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    activity_id = data.get("activity_id")
    
    if not activity_id:
        return {"status": "error", "message": "缺少活动ID参数"}
    
    conn = get_conn()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            # 查询活动是否存在
            cursor.execute("SELECT * FROM activities WHERE activityId = %s", (activity_id,))
            activity = cursor.fetchone()
            
            if not activity:
                return {"status": "error", "message": "活动不存在"}
            
            # 简化查询 - 获取所有活动参与者，后续在Python中过滤
            participants_sql = """
            SELECT ap.*, u.username, u.userAvatarUrl, u.userSignature 
            FROM activity_participants ap
            JOIN users u ON ap.userId = u.userid
            WHERE ap.activityId = %s
            """
            cursor.execute(participants_sql, (activity_id,))
            participants = cursor.fetchall()
            
            # 处理结果
            result = []
            for p in participants:
                # 格式化时间戳
                if "joinTime" in p and p["joinTime"]:
                    p["joinTime"] = p["joinTime"].strftime("%Y-%m-%d %H:%M:%S")
                if "created_at" in p and p["created_at"]:
                    p["created_at"] = p["created_at"].strftime("%Y-%m-%d %H:%M:%S")
                if "updated_at" in p and p["updated_at"]:
                    p["updated_at"] = p["updated_at"].strftime("%Y-%m-%d %H:%M:%S")
                
                # 解析JSON字段
                if "participantStatus" in p and p["participantStatus"]:
                    try:
                        p["participantStatus"] = json.loads(p["participantStatus"])
                    except:
                        pass
                
                if "status" in p and p["status"]:
                    try:
                        p["status"] = json.loads(p["status"])
                    except:
                        pass
                
                # 处理extraData并检查审核状态
                is_approved = True  # 默认通过
                if "extraData" in p and p["extraData"]:
                    try:
                        extra_data = json.loads(p["extraData"])
                        p["extraData"] = extra_data
                        # 如果有审核状态字段且不是"通过"，则排除
                        if "审核状态" in extra_data and extra_data["审核状态"] != "通过":
                            is_approved = False
                    except:
                        pass
                
                # 只添加审核通过或无审核状态的参与者
                if is_approved:
                    result.append(p)
            
            return {
                "status": "success",
                "data": {
                    "total": len(result),
                    "participants": result
                }
            }
    except Exception as e:
        import traceback
        error_details = traceback.format_exc()
        return {"status": "error", "message": f"获取活动参与者失败: {str(e)}", "details": error_details}
    finally:
        conn.close()



# 查询用户发布的活动的所有参与者
@app.post("/publisher/activity/participants")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_publisher_activity_participants(request: Request):
    try:
        data = get_decrypted_data(request)  # 获取解密后的数据
        publisher_id = data.get("userid")
        
        # 获取分页参数，默认每页30条，第1页
        page = data.get("page", 1)
        limit = data.get("limit", 30)
        
        # 确保参数类型正确
        try:
            page = int(page)
            limit = int(limit)
            if page < 1:
                page = 1
            if limit < 1:
                limit = 30
        except:
            page = 1
            limit = 30
        
        # 计算偏移量
        offset = (page - 1) * limit
        
        if not publisher_id:
            return {"status": "error", "message": "缺少发布者ID参数"}
        
        conn = get_conn()
        try:
            with conn.cursor(pymysql.cursors.DictCursor) as cursor:
                # 1. 查询用户发布的正在进行中的活动
                activities_sql = """
                SELECT activityId, activityTitle, activityType, activityMaxParticipants, 
                       activityCurrentParticipants, activityPublishTime
                FROM activities
                WHERE publisherId = %s 
                AND JSON_CONTAINS(activityStatus, '"进行中"')
                ORDER BY activityPublishTime DESC
                """
                cursor.execute(activities_sql, (publisher_id,))
                activities = cursor.fetchall()
                
                if not activities:
                    return {"status": "success", "data": {
                        "total": 0,
                        "participants": []
                    }}
                
                # 获取所有活动ID
                activity_ids = [activity["activityId"] for activity in activities]
                
                # 构建格式化的活动信息字典，方便后续使用
                activity_info = {}
                for activity in activities:
                    activity_id = activity["activityId"]
                    activity_info[activity_id] = {
                        "activityId": activity_id,
                        "activityTitle": activity["activityTitle"],
                        "activityType": json.loads(activity["activityType"]) if activity["activityType"] else [],
                        "activityMaxParticipants": activity["activityMaxParticipants"],
                        "activityCurrentParticipants": activity["activityCurrentParticipants"],
                        "activityPublishTime": activity["activityPublishTime"].strftime("%Y-%m-%d %H:%M:%S") if activity["activityPublishTime"] else None
                    }
                
                # 2. 获取参与者总数（用于分页）- 排除发布者自己
                count_sql = """
                SELECT COUNT(*) as total
                FROM activity_participants
                WHERE activityId IN ({}) AND userId != %s
                """.format(','.join(['%s'] * len(activity_ids)))
                
                cursor.execute(count_sql, activity_ids + [publisher_id])
                total_count = cursor.fetchone()['total']
                total_pages = (total_count + limit - 1) // limit  # 计算总页数
                
                # 3. 查询所有活动的参与者，添加userCity字段，加入分页 - 排除发布者自己
                participants_sql = """
                SELECT ap.activityId, ap.userId, ap.joinTime, ap.participantStatus, ap.extraData,
                       u.username, u.userAvatarUrl, u.userSignature, u.userGender, u.userAge, u.userTags,
                       u.userBirthday, u.userPoints, u.userCity, u.extraData as userExtraData
                FROM activity_participants ap
                JOIN users u ON ap.userId = u.userid
                WHERE ap.activityId IN ({}) AND ap.userId != %s
                ORDER BY ap.joinTime DESC
                LIMIT %s OFFSET %s
                """.format(','.join(['%s'] * len(activity_ids)))
                
                # 添加分页参数和过滤条件
                query_params = activity_ids + [publisher_id, limit, offset]
                cursor.execute(participants_sql, query_params)
                all_participants = cursor.fetchall()
                
                # 4. 整理参与者信息
                result_participants = []
                for participant in all_participants:
                    activity_id = participant["activityId"]
                    
                    # 解析JSON字段
                    extra_data = json.loads(participant["extraData"]) if participant["extraData"] else {}
                    participant_status = json.loads(participant["participantStatus"]) if participant["participantStatus"] else []
                    user_tags = json.loads(participant["userTags"]) if participant["userTags"] else []
                    user_extra_data = json.loads(participant["userExtraData"]) if participant["userExtraData"] else {}
                    
                    # 检查审核状态，获取是否通过
                    review_status = extra_data.get("审核状态", "审核中")
                    
                    # 格式化生日
                    formatted_birthday = participant["userBirthday"].strftime("%Y-%m-%d") if participant["userBirthday"] else None
                    
                    # 构建参与者信息
                    participant_info = {
                        "userId": participant["userId"],
                        "username": participant["username"],
                        "userAvatar": participant["userAvatarUrl"],
                        "userSignature": participant["userSignature"],
                        "userGender": participant["userGender"],
                        "userAge": participant["userAge"],
                        "userCity": participant["userCity"],  # 添加城市字段
                        "userTags": user_tags,
                        "userBirthday": formatted_birthday,  # 添加生日
                        "userPoints": participant["userPoints"] or 0,  # 添加积分
                        "userExtraData": user_extra_data,  # 添加用户extraData
                        "activityId": activity_id,
                        "activityTitle": activity_info[activity_id]["activityTitle"],
                        "activityType": activity_info[activity_id]["activityType"],
                        "joinTime": participant["joinTime"].strftime("%Y-%m-%d %H:%M:%S") if participant["joinTime"] else None,
                        "participantStatus": participant_status,
                        "reviewStatus": review_status,
                        "isApproved": review_status == "通过"
                    }
                    
                    result_participants.append(participant_info)
                
                return {
                    "status": "success",
                    "data": {
                        "total": total_count,
                        "activities": [activity_info[aid] for aid in activity_info],
                        "participants": result_participants,
                        "pagination": {
                            "page": page,
                            "limit": limit,
                            "total_pages": total_pages
                        }
                    }
                }
                
        finally:
            conn.close()
    except Exception as e:
        import traceback
        error_details = traceback.format_exc()
        return {"status": "error", "message": f"查询活动参与者失败: {str(e)}", "details": error_details}


# 添加活动参与者
# 添加活动参与者
@app.post("/activities/participants/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def add_activity_participant(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    activity_id = data.get("activityId")
    user_id = data.get("userId")
    
    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查活动是否存在且未满员，同时获取发布者ID
            check_sql = """
            SELECT activityCurrentParticipants, activityMaxParticipants, publisherId
            FROM activities 
            WHERE activityId = %s
            """
            cursor.execute(check_sql, (activity_id,))
            activity = cursor.fetchone()
            
            if not activity:
                return {"status": "error", "message": "活动不存在或已关闭"}
            
            current, maximum, publisher_id = activity
            if current >= maximum:
                return {"status": "error", "message": "活动已满员"}
            
            # 检查用户是否已参加
            check_participant_sql = """
            SELECT * FROM activity_participants 
            WHERE activityId = %s AND userId = %s
            """
            cursor.execute(check_participant_sql, (activity_id, user_id))
            if cursor.fetchone():
                return {"status": "error", "message": "您已参加该活动，不能重复参加"}
            
            # 判断申请人是否为活动发布者
            is_publisher = int(user_id) == int(publisher_id)
            
            # 设置审核状态和参与状态
            participant_status = json.dumps([data.get("participantStatus", "已报名")])
            
            if is_publisher:
                # 发布者直接通过审核
                extra_data = data.get("extraData", '{"审核状态": "通过"}')
            else:
                # 普通用户需要审核
                extra_data = data.get("extraData", '{"审核状态": "审核中"}')
            
            # 添加参与者
            insert_sql = """
            INSERT INTO activity_participants 
            (activityId, userId, participantStatus, status, extraData) 
            VALUES (%s, %s, %s, %s, %s)
            """
            cursor.execute(insert_sql, (
                activity_id, user_id, participant_status, '["开启"]', extra_data
            ))
            
            # 如果是发布者，更新活动当前参与人数
            if is_publisher:
                update_sql = """
                UPDATE activities 
                SET activityCurrentParticipants = activityCurrentParticipants + 1 
                WHERE activityId = %s
                """
                cursor.execute(update_sql, (activity_id,))
                message = "发布者参与成功"
            else:
                message = "已发送申请"
            
            conn.commit()
            return {"status": "success", "message": message}
    except Exception as e:
        return {"status": "error", "message": f"参加活动失败: {str(e)}"}
    finally:
        conn.close()



# 根据参与者ID查询参与过的活动
@app.post("/activities/participants/history")
async def get_user_participated_activities(request: Request):
    data = await request.json()
    user_id = data.get("userId")
    page = data.get("page", 1)
    page_size = data.get("pageSize", 30)
    
    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}
    
    # 验证分页参数
    try:
        page = max(1, int(page))
        page_size = max(1, min(100, int(page_size)))  # 限制最大页面大小为100
    except (ValueError, TypeError):
        return {"status": "error", "message": "分页参数格式错误"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询总数
            count_sql = """
            SELECT COUNT(*) as total
            FROM activity_participants ap
            INNER JOIN activities a ON ap.activityId = a.activityId
            WHERE ap.userId = %s
            """
            cursor.execute(count_sql, (user_id,))
            total_result = cursor.fetchone()
            total = total_result['total'] if total_result else 0
            
            # 计算总页数
            total_pages = (total + page_size - 1) // page_size
            
            # 计算偏移量
            offset = (page - 1) * page_size
            
            # 查询分页数据
            query_sql = """
            SELECT 
                a.activityId,
                a.activityTitle,
                a.activityImages,
                a.activityType,
                a.activityTags,
                a.activityDetails,
                a.activityLocation,
                ST_X(a.activityCoordinates) as longitude,
                ST_Y(a.activityCoordinates) as latitude,
                a.activityPublishTime,
                a.activityCategory,
                a.activityRestriction,
                a.activityCurrentParticipants,
                a.activityMaxParticipants,
                a.publisherId,
                a.publisherAvatar,
                a.publisherNickname,
                a.publisherSignature,
                a.publisherTags,
                a.activityStatus,
                a.status as activityRecordStatus,
                a.extraData as activityExtraData,
                a.created_at as activityCreatedAt,
                a.updated_at as activityUpdatedAt,
                ap.id as participantRecordId,
                ap.joinTime,
                ap.participantStatus,
                ap.status as participantRecordStatus,
                ap.extraData as participantExtraData,
                ap.created_at as participantCreatedAt,
                ap.updated_at as participantUpdatedAt
            FROM activity_participants ap
            INNER JOIN activities a ON ap.activityId = a.activityId
            WHERE ap.userId = %s
            ORDER BY ap.joinTime DESC
            LIMIT %s OFFSET %s
            """
            
            cursor.execute(query_sql, (user_id, page_size, offset))
            activities = cursor.fetchall()
            
            # 格式化结果
            result_activities = []
            for activity in activities:
                # 解析JSON字段
                json_fields = [
                    'activityImages', 'activityType', 'activityTags', 'activityCategory',
                    'activityRestriction', 'publisherTags', 'activityStatus', 
                    'activityRecordStatus', 'activityExtraData', 'participantStatus',
                    'participantRecordStatus', 'participantExtraData'
                ]
                
                for field in json_fields:
                    if activity[field]:
                        try:
                            activity[field] = json.loads(activity[field])
                        except (json.JSONDecodeError, TypeError):
                            activity[field] = activity[field]
                
                # 格式化时间字段
                time_fields = ['activityPublishTime', 'joinTime', 'activityCreatedAt', 
                              'activityUpdatedAt', 'participantCreatedAt', 'participantUpdatedAt']
                for field in time_fields:
                    if activity[field]:
                        activity[field] = activity[field].strftime('%Y-%m-%d %H:%M:%S')
                
                # 重新组织数据结构
                formatted_activity = {
                    "activityInfo": {
                        "activityId": activity["activityId"],
                        "activityTitle": activity["activityTitle"],
                        "activityImages": activity["activityImages"],
                        "activityType": activity["activityType"],
                        "activityTags": activity["activityTags"],
                        "activityDetails": activity["activityDetails"],
                        "activityLocation": activity["activityLocation"],
                        "longitude": activity["longitude"],
                        "latitude": activity["latitude"],
                        "activityPublishTime": activity["activityPublishTime"],
                        "activityCategory": activity["activityCategory"],
                        "activityRestriction": activity["activityRestriction"],
                        "activityCurrentParticipants": activity["activityCurrentParticipants"],
                        "activityMaxParticipants": activity["activityMaxParticipants"],
                        "publisherId": activity["publisherId"],
                        "publisherAvatar": activity["publisherAvatar"],
                        "publisherNickname": activity["publisherNickname"],
                        "publisherSignature": activity["publisherSignature"],
                        "publisherTags": activity["publisherTags"],
                        "activityStatus": activity["activityStatus"],
                        "status": activity["activityRecordStatus"],
                        "extraData": activity["activityExtraData"],
                        "created_at": activity["activityCreatedAt"],
                        "updated_at": activity["activityUpdatedAt"]
                    },
                    "participantInfo": {
                        "participantRecordId": activity["participantRecordId"],
                        "joinTime": activity["joinTime"],
                        "participantStatus": activity["participantStatus"],
                        "status": activity["participantRecordStatus"],
                        "extraData": activity["participantExtraData"],
                        "created_at": activity["participantCreatedAt"],
                        "updated_at": activity["participantUpdatedAt"]
                    }
                }
                
                result_activities.append(formatted_activity)
            
            return {
                "status": "success",
                "message": "查询成功",
                "data": {
                    "activities": result_activities,
                    "pagination": {
                        "currentPage": page,
                        "pageSize": page_size,
                        "totalPages": total_pages,
                        "totalItems": total,
                        "hasNextPage": page < total_pages,
                        "hasPrevPage": page > 1
                    }
                }
            }
            
    except Exception as e:
        return {"status": "error", "message": f"查询参与活动失败: {str(e)}"}
    finally:
        conn.close()



# 删除活动参与者
@app.post("/activities/participants/delete")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def delete_activity_participant(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    activity_id = data.get("activityId")
    user_id = data.get("userId")
    
    if not activity_id or not user_id:
        return {"status": "error", "message": "缺少活动ID或用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查参与者是否存在，同时获取审核状态
            check_sql = """
            SELECT extraData FROM activity_participants 
            WHERE activityId = %s AND userId = %s
            """
            cursor.execute(check_sql, (activity_id, user_id))
            participant = cursor.fetchone()
            
            if not participant:
                return {"status": "error", "message": "该用户未参加此活动"}
            
            # 解析extraData获取审核状态
            should_decrease_count = False
            try:
                extra_data = json.loads(participant[0]) if participant[0] else {}
                review_status = extra_data.get("审核状态", "")
                # 只有在审核状态为"通过"时才减少活动参与人数
                should_decrease_count = review_status == "通过"
            except:
                # 如果解析失败，为安全起见不减少计数
                pass
            
            # 删除参与者
            delete_sql = """
            DELETE FROM activity_participants 
            WHERE activityId = %s AND userId = %s
            """
            cursor.execute(delete_sql, (activity_id, user_id))
            
            # 如果是已审核通过的参与者，才更新活动当前参与人数
            if should_decrease_count:
                update_sql = """
                UPDATE activities 
                SET activityCurrentParticipants = GREATEST(activityCurrentParticipants - 1, 0)
                WHERE activityId = %s
                """
                cursor.execute(update_sql, (activity_id,))
            
            conn.commit()
            return {"status": "success", "message": "成功移除活动参与者"}
    except Exception as e:
        return {"status": "error", "message": f"移除参与者失败: {str(e)}"}
    finally:
        conn.close()

# 修改活动参与者审核状态
@app.post("/activities/participants/review")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def update_participant_review_status(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    
    activity_id = data.get("activityId")
    user_id = data.get("userId")
    review_status = data.get("reviewStatus")  # 接收审核状态参数："通过" 或 "未通过"
    
    if not activity_id or not user_id or not review_status:
        return {"status": "error", "message": "缺少必要参数"}
    
    if review_status not in ["通过", "未通过"]:
        return {"status": "error", "message": "审核状态参数不正确，应为'通过'或'未通过'"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查参与记录是否存在
            check_sql = """
            SELECT extraData FROM activity_participants 
            WHERE activityId = %s AND userId = %s
            """
            cursor.execute(check_sql, (activity_id, user_id))
            participant = cursor.fetchone()
            
            if not participant:
                return {"status": "error", "message": "未找到对应的活动参与记录"}
            
            # 查询活动名称
            activity_sql = """
            SELECT activityTitle FROM activities WHERE activityId = %s
            """
            cursor.execute(activity_sql, (activity_id,))
            activity_result = cursor.fetchone()
            activity_name = activity_result[0] if activity_result else "未知活动"
            
            # 查询用户名称
            user_sql = """
            SELECT username FROM users WHERE userid = %s
            """
            cursor.execute(user_sql, (user_id,))
            user_result = cursor.fetchone()
            user_name = user_result[0] if user_result else "未知用户"
            
            # 解析原有的extraData
            try:
                extra_data = json.loads(participant[0]) if participant[0] else {}
            except:
                extra_data = {}
            
            # 获取原审核状态
            old_status = extra_data.get("审核状态", "")
            
            # 更新审核状态
            extra_data["审核状态"] = review_status
            
            # 更新数据库
            update_sql = """
            UPDATE activity_participants 
            SET extraData = %s 
            WHERE activityId = %s AND userId = %s
            """
            cursor.execute(update_sql, (json.dumps(extra_data), activity_id, user_id))
            
            conn.commit()
            
            # 根据状态变化更新活动参与人数
            if old_status == "通过" and review_status == "未通过":
                # 从通过改为未通过，参与人数减1
                update_count_sql = """
                UPDATE activities 
                SET activityCurrentParticipants = GREATEST(0, activityCurrentParticipants - 1) 
                WHERE activityId = %s
                """
                cursor.execute(update_count_sql, (activity_id,))
                conn.commit()
            elif old_status != "通过" and review_status == "通过":
                # 从未通过改为通过，检查是否还有名额，如果有则参与人数加1
                check_activity_sql = """
                SELECT activityCurrentParticipants, activityMaxParticipants 
                FROM activities 
                WHERE activityId = %s
                """
                cursor.execute(check_activity_sql, (activity_id,))
                activity = cursor.fetchone()
                
                if activity:
                    current, maximum = activity
                    if current < maximum:
                        update_count_sql = """
                        UPDATE activities 
                        SET activityCurrentParticipants = activityCurrentParticipants + 1 
                        WHERE activityId = %s
                        """
                        cursor.execute(update_count_sql, (activity_id,))
                        conn.commit()
        
        # 根据审核状态返回不同的消息
        if review_status == "通过":
            return {
                "status": "success", 
                "message": f"成功将用户 {user_name} 的活动「{activity_name}」审核状态更新为「通过」",
                "userMessage": f"已允许 {user_name} 加入「{activity_name}」活动",
                "participantMessage": f"尊敬的 {user_name}，您已成功参与「{activity_name}」活动，请准时参加"
            }
        else:  # 审核状态为"未通过"
            return {
                "status": "success", 
                "message": f"成功将用户 {user_name} 的活动「{activity_name}」审核状态更新为「未通过」",
                "userMessage": f"已拒绝 {user_name} 加入「{activity_name}」活动",
                "participantMessage": f"很抱歉，您参与「{activity_name}」活动的申请未获通过"
            }
    except Exception as e:
        import traceback
        error_details = traceback.format_exc()
        return {"status": "error", "message": f"更新审核状态失败: {str(e)}", "details": error_details}
    finally:
        conn.close()


# 更新参与者状态
@app.put("/activities/{activity_id}/participants/{user_id}")
async def update_participant_status(activity_id: int, user_id: int, request: Request):
    data = await request.json()
    new_status = data.get("participantStatus")
    
    if not new_status:
        return {"status": "error", "message": "缺少状态信息"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查参与记录是否存在
            check_sql = """
            SELECT * FROM activity_participants 
            WHERE activityId = %s AND userId = %s
            """
            cursor.execute(check_sql, (activity_id, user_id))
            if not cursor.fetchone():
                return {"status": "error", "message": "参与记录不存在或已关闭"}
            
            # 更新状态
            update_sql = """
            UPDATE activity_participants 
            SET participantStatus = %s, extraData = JSON_MERGE_PATCH(extraData, %s)
            WHERE activityId = %s AND userId = %s
            """
            
            extra_data = data.get("extraData", "{}")
            cursor.execute(update_sql, (new_status, extra_data, activity_id, user_id))
            
            # 如果状态是"已取消"，更新活动当前参与人数
            if new_status == "已取消":
                update_activity_sql = """
                UPDATE activities 
                SET activityCurrentParticipants = activityCurrentParticipants - 1 
                WHERE activityId = %s AND activityCurrentParticipants > 0
                """
                cursor.execute(update_activity_sql, (activity_id,))
            
            conn.commit()
            return {"status": "success", "message": "参与状态更新成功"}
    except Exception as e:
        return {"status": "error", "message": f"更新参与状态失败: {str(e)}"}
    finally:
        conn.close()






# 根据经纬度查询附近的评论
# 根据经纬度查询附近的评论
# 根据经纬度查询附近的评论
@app.post("/index/nearby/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_nearby_comments(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    latitude = data.get("latitude")  # 纬度
    longitude = data.get("longitude")  # 经度
    shop_limit = data.get("shop_limit", 10)  # 店铺默认显示数量
    shop_page = data.get("page", 1)  # 店铺默认页码
    user_id = data.get("userId")  # 添加用户ID参数，用于检查点赞状态
    category = data.get("Category")  # 获取分类参数
    
    if not latitude or not longitude:
        return {"status": "error", "message": "缺少经纬度信息"}
    
    # 店铺的分页偏移量
    shop_offset = (shop_page - 1) * shop_limit
    
    # 处理类别筛选参数
    filter_category = []
    if category:
        # 确保category是一个列表
        if isinstance(category, str):
            try:
                category = json.loads(category)
            except json.JSONDecodeError:
                category = [category]
        # 清除列表中的空值和None
        filter_category = [cat for cat in category if cat]
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 1. 查询最近的一条评论，添加类别筛选条件
            latest_comment_sql = """
            SELECT 
                c.commentId, c.commentTitle, c.commentContent, c.commentImages, 
                c.commentCategory, c.commentTags, c.commentLocation, 
                ST_X(c.commentCoordinates) as longitude, 
                ST_Y(c.commentCoordinates) as latitude,
                c.commentPublishTime, c.shoppingImages,
                c.publisherId,
                u.userAvatarUrl as publisherAvatar,
                u.username as publisherNickname,
                u.userSignature as publisherSignature,
                u.userTags as publisherTags,
                u.userPoints as publisherPoints,
                c.likeCount, c.commentCount, c.shareCount,
                c.status, c.extraData,
                ST_Distance_Sphere(
                    c.commentCoordinates, 
                    POINT(%s, %s)
                ) as distance,
                IFNULL(cl.status, 0) as is_liked
            FROM comments c
            LEFT JOIN users u ON c.publisherId = u.userid
            LEFT JOIN comment_likes cl ON c.commentId = cl.commentId AND cl.userId = %s
            WHERE NOT JSON_CONTAINS(c.status, '"删除"')
            """
            latest_comment_params = [longitude, latitude, user_id if user_id else 0]
            
            # 添加类别筛选条件
            if filter_category:
                category_conditions = []
                for cat in filter_category:
                    # 修改为使用JSON_CONTAINS函数正确处理JSON字段
                    category_conditions.append("JSON_CONTAINS(c.commentCategory, JSON_QUOTE(%s))")
                    latest_comment_params.append(cat)
                
                if category_conditions:
                    latest_comment_sql += f" AND ({' OR '.join(category_conditions)})"
            
            latest_comment_sql += " ORDER BY c.commentPublishTime DESC LIMIT 1"
            
            cursor.execute(latest_comment_sql, latest_comment_params)
            latest_comment = cursor.fetchone()
            
            # 2. 查询附近的商铺，添加时间权重
            shops_sql = """
            SELECT 
                s.shopId, s.shopName, s.shopType, s.shopDescription, 
                s.shopImages, s.shopCategory, s.shopTags, s.shopLocation, 
                ST_X(s.shopCoordinates) as longitude, 
                ST_Y(s.shopCoordinates) as latitude,
                s.shopOpenTime, s.shopPhone, s.shopAddress, 
                s.ownerId, s.ownerName, s.shopRating, s.shopCommentIds,
                s.shopCommentCount, s.shopGoodReviews, s.shopBadReviews,
                s.shopLikeCount, s.shopCheckInCount, s.shopStatus, 
                s.verifyStatus, s.shopFollowers, s.shopViews, s.shopSales,
                s.shopLevel, s.shopNotice, s.shopFeatures, s.shopPromotions, 
                s.status, s.extraData, s.created_at,
                ST_Distance_Sphere(
                    s.shopCoordinates, 
                    POINT(%s, %s)
                ) as distance,
                TIMESTAMPDIFF(MINUTE, s.created_at, NOW()) as minutes_ago,
                (GREATEST(1000, ST_Distance_Sphere(
                    s.shopCoordinates, 
                    POINT(%s, %s)
                )) * GREATEST(1, LEAST(20, TIMESTAMPDIFF(MINUTE, s.created_at, NOW()) * 0.01))) as weight_score
            FROM shops s
            WHERE NOT JSON_CONTAINS(s.status, '"删除"')
            AND s.shopStatus = 1 -- 只选择营业中的商铺
            """
            
            shops_params = [longitude, latitude, longitude, latitude]
            
            # 添加商铺类别筛选条件
            if filter_category:
                category_conditions = []
                for cat in filter_category:
                    # 使用JSON_CONTAINS函数正确处理JSON字段
                    category_conditions.append("JSON_CONTAINS(s.shopCategory, JSON_QUOTE(%s))")
                    shops_params.append(cat)
                
                if category_conditions:
                    shops_sql += f" AND ({' OR '.join(category_conditions)})"
            
            # 修改排序，使用权重分数
            shops_sql += " ORDER BY weight_score LIMIT %s OFFSET %s"
            shops_params.extend([shop_limit, shop_offset])
            
            cursor.execute(shops_sql, shops_params)
            shops = cursor.fetchall()
            
            # 3. 查询商铺总数，用于分页
            shops_count_sql = """
            SELECT COUNT(*) AS total 
            FROM shops 
            WHERE NOT JSON_CONTAINS(status, '"删除"')
            AND shopStatus = 1
            """
            
            # 同样为计数SQL添加筛选条件
            count_params = []
            if filter_category:
                category_conditions = []
                for cat in filter_category:
                    # 使用JSON_CONTAINS函数正确处理JSON字段
                    category_conditions.append("JSON_CONTAINS(shopCategory, JSON_QUOTE(%s))")
                    count_params.append(cat)
                
                if category_conditions:
                    shops_count_sql += f" AND ({' OR '.join(category_conditions)})"
            
            cursor.execute(shops_count_sql, count_params)
            shop_total_count = cursor.fetchone()['total']
            shop_total_pages = (shop_total_count + shop_limit - 1) // shop_limit
            
            # 4. 处理JSON字段并查询每个商铺的最新评论
            processed_shops = []
            for shop in shops:
                # 处理商铺的JSON字段
                for field in ['shopImages', 'shopCategory', 'shopTags', 'shopCommentIds', 
                             'shopFeatures', 'shopPromotions', 'status', 'extraData']:
                    if shop.get(field):
                        if isinstance(shop[field], str):
                            try:
                                shop[field] = json.loads(shop[field])
                            except json.JSONDecodeError:
                                if field in ['shopImages', 'shopCategory', 'shopTags', 'shopCommentIds', 'shopFeatures', 'shopPromotions']:
                                    shop[field] = []
                                else:
                                    shop[field] = {}
                
                # 将距离转换为公里
                if shop.get('distance'):
                    shop['distance'] = round(shop['distance'] / 1000, 2)
                
                # 获取最新的一条评论ID
                latest_comment_id = None
                if shop.get('shopCommentIds') and isinstance(shop['shopCommentIds'], list) and shop['shopCommentIds']:
                    try:
                        # 如果有多个评论ID，获取最新添加的一个
                        latest_comment_id = shop['shopCommentIds'][-1]
                    except (IndexError, TypeError):
                        pass
                
                # 如果找到了评论ID，查询评论详情
                shop['latestComment'] = None
                if latest_comment_id:
                    comment_sql = """
                    SELECT 
                        c.commentId, c.commentTitle, c.commentContent, c.commentImages, 
                        c.commentCategory, c.commentTags, c.commentLocation, 
                        c.commentPublishTime, c.shoppingImages,
                        c.publisherId, 
                        u.userAvatarUrl as publisherAvatar,
                        u.username as publisherNickname,
                        u.userSignature as publisherSignature,
                        u.userTags as publisherTags,
                        u.userPoints as publisherPoints,
                        c.likeCount, c.commentCount, c.extraData
                    FROM comments c
                    LEFT JOIN users u ON c.publisherId = u.userid
                    WHERE c.commentId = %s AND NOT JSON_CONTAINS(c.status, '"删除"')
                    """
                    cursor.execute(comment_sql, (latest_comment_id,))
                    comment = cursor.fetchone()
                    
                    if comment:
                        # 处理评论的JSON字段
                        for field in ['commentImages', 'commentCategory', 'commentTags', 'shoppingImages', 'extraData' ,'publisherTags']:
                            if comment.get(field):
                                if isinstance(comment[field], str):
                                    try:
                                        comment[field] = json.loads(comment[field])
                                    except json.JSONDecodeError:
                                        if field in ['commentImages', 'commentCategory', 'commentTags', 'shoppingImages' ,'publisherTags']:
                                            comment[field] = []
                                        else:
                                            comment[field] = {}
                        
                        shop['latestComment'] = comment
                
                # 只有当latestComment不为null时才添加该店铺信息到结果中
                if shop['latestComment'] is not None:
                    processed_shops.append(shop)
            
            # 5. 处理最新评论的JSON字段
            if latest_comment:
                for field in ['commentImages', 'commentCategory', 'commentTags', 'publisherTags', 
                             'status', 'extraData', 'shoppingImages']:
                    if latest_comment.get(field):
                        if isinstance(latest_comment[field], str):
                            try:
                                latest_comment[field] = json.loads(latest_comment[field])
                            except json.JSONDecodeError:
                                if field in ['commentImages', 'commentCategory', 'commentTags', 'publisherTags', 'shoppingImages']:
                                    latest_comment[field] = []
                                else:
                                    latest_comment[field] = {}
                
                # 将距离转换为公里
                if latest_comment.get('distance'):
                    latest_comment['distance'] = round(latest_comment['distance'] / 1000, 2)
            
            # 返回结果
            return {
                "status": "success", 
                "data_comment": latest_comment,  # 最近的一条评论
                "data_shop": processed_shops,   # 附近的商铺及其最新评论
                "pagination": {
                    "total": shop_total_count,
                    "page": shop_page,
                    "limit": shop_limit,
                    "total_pages": shop_total_pages
                }
            }
    except Exception as e:
        return {"status": "error", "message": f"查询失败: {str(e)}"}
    finally:
        conn.close()


# 根据经纬度查询附近的评论
# 根据经纬度查询附近的评论
@app.post("/comments/nearby/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_nearby_comments(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    latitude = data.get("latitude")  # 纬度
    longitude = data.get("longitude")  # 经度
    commentCategory = data.get("Category")  # 评论分类
    limit = data.get("limit", 16)  # 默认每页20条
    page = data.get("page", 1)  # 默认第1页
    
    if not latitude or not longitude:
        return {"status": "error", "message": "缺少经纬度信息"}
    
    # 计算偏移量
    offset = (page - 1) * limit
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 构建基础SQL查询，添加时间权重计算
            base_sql = """
            SELECT 
                c.commentId, c.commentTitle, c.commentContent, c.commentImages, 
                c.commentCategory, c.commentTags, c.commentLocation, 
                ST_X(c.commentCoordinates) as longitude, 
                ST_Y(c.commentCoordinates) as latitude,
                c.commentPublishTime, c.shoppingImages,
                c.publisherId,
                u.userAvatarUrl as publisherAvatar,
                u.username as publisherNickname,
                u.userSignature as publisherSignature,
                u.userTags as publisherTags,
                u.userPoints as publisherPoints,
                c.likeCount, c.commentCount, c.shareCount,
                c.status, c.extraData,
                ST_Distance_Sphere(
                    c.commentCoordinates, 
                    POINT(%s, %s)
                ) as distance,
                TIMESTAMPDIFF(MINUTE, c.commentPublishTime, NOW()) as minutes_ago,
                (GREATEST(1000, ST_Distance_Sphere(
                    c.commentCoordinates, 
                    POINT(%s, %s)
                )) * GREATEST(1, LEAST(20, TIMESTAMPDIFF(MINUTE, c.commentPublishTime, NOW()) * 0.01))) as weight_score,
                CASE WHEN cl.likeId IS NOT NULL AND cl.status = 1 THEN 1 ELSE 0 END as is_liked
            FROM comments c
            LEFT JOIN users u ON c.publisherId = u.userid
            LEFT JOIN comment_likes cl ON c.commentId = cl.commentId AND cl.userId = %s
            WHERE NOT JSON_CONTAINS(c.status, '"删除"')
            """
            user_id = data.get("userId", 0)  # 添加用户ID参数
            params = [longitude, latitude, longitude, latitude, user_id]  # 需要五个参数匹配SQL中的五个%s
            
            # 添加分类筛选条件 - 修改为使用JSON_CONTAINS
            if commentCategory:
                # 确保 commentCategory 是列表格式
                if isinstance(commentCategory, str):
                    try:
                        commentCategory = json.loads(commentCategory)
                    except json.JSONDecodeError:
                        commentCategory = [commentCategory]
                
                if not isinstance(commentCategory, list):
                    commentCategory = [commentCategory]
                
                # 构建 OR 条件，查找包含任一分类的评论
                category_conditions = []
                for category in commentCategory:
                    if category:  # 跳过空值
                        # 使用 JSON_CONTAINS 函数检查数组中是否包含特定值
                        category_conditions.append("JSON_CONTAINS(commentCategory, JSON_QUOTE(%s))")
                        params.append(category)
                
                if category_conditions:
                    base_sql += f" AND ({' OR '.join(category_conditions)})"
            
            # 修改排序条件，使用权重分数排序
            base_sql += " ORDER BY weight_score LIMIT %s OFFSET %s"
            params.extend([limit, offset])
            
            # 执行查询
            cursor.execute(base_sql, params)
            comments = cursor.fetchall()
            
            # 查询总记录数（用于分页信息）
            count_sql = """
            SELECT COUNT(*) as total FROM comments 
            WHERE NOT JSON_CONTAINS(status, '"删除"')
            """
            
            count_params = []
            
            # 添加分类筛选条件到计数查询
            if commentCategory:
                # 构建与上面相同的分类条件
                category_conditions = []
                for category in commentCategory:
                    if category:  # 跳过空值
                        # 使用 JSON_CONTAINS 函数检查数组中是否包含特定值
                        category_conditions.append("JSON_CONTAINS(commentCategory, JSON_QUOTE(%s))")
                        count_params.append(category)
                
                if category_conditions:
                    count_sql += f" AND ({' OR '.join(category_conditions)})"
            
            cursor.execute(count_sql, count_params)
            total_count = cursor.fetchone()['total']
            total_pages = (total_count + limit - 1) // limit  # 计算总页数
            
            # 处理JSON字段
            for comment in comments:
                # 处理 commentImages 字段
                if comment.get('commentImages'):
                    # 如果是字符串，尝试解析为JSON
                    if isinstance(comment['commentImages'], str):
                        try:
                            comment['commentImages'] = json.loads(comment['commentImages'])
                        except json.JSONDecodeError:
                            comment['commentImages'] = []
                    
                    # 检查是否是带有 values 键的对象
                    if isinstance(comment['commentImages'], dict) and 'values' in comment['commentImages']:
                        comment['commentImages'] = comment['commentImages']['values']
                
                # 处理 shoppingImages 字段
                if comment.get('shoppingImages'):
                    # 如果是字符串，尝试解析为JSON
                    if isinstance(comment['shoppingImages'], str):
                        try:
                            comment['shoppingImages'] = json.loads(comment['shoppingImages'])
                        except json.JSONDecodeError:
                            comment['shoppingImages'] = []
                    
                    # 检查是否是带有 values 键的对象
                    if isinstance(comment['shoppingImages'], dict) and 'values' in comment['shoppingImages']:
                        comment['shoppingImages'] = comment['shoppingImages']['values']
                
                # 处理其他JSON字段
                for field in ['commentTags', 'publisherTags', 'status', 'extraData', 'commentCategory']:
                    if comment.get(field):
                        if isinstance(comment[field], str):
                            try:
                                comment[field] = json.loads(comment[field])
                            except json.JSONDecodeError:
                                # 如果解析失败，设置为空列表或空对象
                                comment[field] = [] if field in ['commentTags', 'publisherTags', 'commentCategory'] else {}
                
                # 将距离转换为公里
                if comment.get('distance'):
                    comment['distance'] = round(comment['distance'] / 1000, 2)  # 转换为公里并保留两位小数
            
            print(f"找到 {len(comments)} 条评论")
            
            # 返回结果，包含分页信息
            return {
                "status": "success", 
                "data": comments,
                "pagination": {
                    "total": total_count,
                    "page": page,
                    "limit": limit,
                    "total_pages": total_pages
                }
            }
    except Exception as e:
        print(f"查询评论失败: {str(e)}")
        return {"status": "error", "message": f"查询评论失败: {str(e)}"}
    finally:
        conn.close()










# 根据评论ID获取评论详情（POST方法）
@app.post("/comments/detail/")
async def get_comment_detail_post(request: Request):
    data = await request.json()
    comment_id = data.get("commentId")
    
    if not comment_id:
        return {"status": "error", "message": "缺少评论ID"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询评论详情
            sql = """
            SELECT 
                c.*,
                ST_X(c.commentCoordinates) as longitude, 
                ST_Y(c.commentCoordinates) as latitude,
                COUNT(cr.replyId) as replyCount
            FROM comments c
            LEFT JOIN comment_replies cr ON c.commentId = cr.commentId
            WHERE c.commentId = %s
            GROUP BY c.commentId
            """
            cursor.execute(sql, (comment_id,))
            comment = cursor.fetchone()
            
            if not comment:
                return {"status": "error", "message": "评论不存在或已删除"}
            
            # 处理JSON字段
            if comment.get('commentImages'):
                comment['commentImages'] = json.loads(comment['commentImages'])
            if comment.get('commentTags'):
                comment['commentTags'] = json.loads(comment['commentTags'])
            if comment.get('shoppingImages'):
                comment['shoppingImages'] = json.loads(comment['shoppingImages'])
            if comment.get('publisherTags'):
                comment['publisherTags'] = json.loads(comment['publisherTags'])
            if comment.get('extraData'):
                comment['extraData'] = json.loads(comment['extraData'])
            
            # 查询评论的回复
            replies_sql = """
            SELECT 
                replyId, commentId, replyContent, replyImages,
                publisherId, publisherAvatar, publisherNickname,
                replyTime, likeCount, parentReplyId, status, extraData
            FROM comment_replies
            WHERE commentId = %s
            ORDER BY 
                CASE WHEN parentReplyId IS NULL THEN replyId ELSE parentReplyId END,
                replyTime
            """
            cursor.execute(replies_sql, (comment_id,))
            replies = cursor.fetchall()
            
            # 处理回复的JSON字段
            for reply in replies:
                if reply.get('replyImages'):
                    reply['replyImages'] = json.loads(reply['replyImages'])
                if reply.get('extraData'):
                    reply['extraData'] = json.loads(reply['extraData'])
            
            # 将回复添加到评论中
            comment['replies'] = replies
            
            return {"status": "success", "data": comment}
    except Exception as e:
        return {"status": "error", "message": f"获取评论详情失败: {str(e)}"}
    finally:
        conn.close()


# 根据用户ID获取用户的所有活动和评论（POST方法）

# ... existing code ...

# 根据用户ID获取用户的所有活动和评论（POST方法）
@app.post("/users/content/")
@require_encryption(["userId"])  # 添加解密装饰器
async def get_user_content_post(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("userId")
    # 新增天数过滤，默认为30天
    days = data.get("days", 30)

    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}

    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            result = {"status": "success"}

            # 计算时间阈值
            threshold_date = datetime.now() - timedelta(days=days)

            # 查询用户发布的活动
            activities_sql = """
            SELECT
                activityId, activityTitle, activityImages, activityType,
                activityTags, activityDetails, activityLocation,
                ST_X(activityCoordinates) as longitude,
                ST_Y(activityCoordinates) as latitude,
                activityPublishTime, activityCategory, activityRestriction,
                activityCurrentParticipants, activityMaxParticipants,
                publisherId, publisherAvatar, publisherNickname,
                publisherSignature, publisherTags, activityStatus,
                status, extraData, updated_at,
                CASE 
                    WHEN JSON_CONTAINS_PATH(extraData, 'one', '$.activityEndTime') 
                    AND STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(extraData, '$.activityEndTime')), '%%Y-%%m-%%d %%H:%%i') < NOW()
                    THEN '活动已结束'
                    ELSE '活动进行中'
                END as activityStatusMessage
            FROM activities
            WHERE NOT JSON_CONTAINS(activityStatus, '"删除"')
            AND publisherId = %s
            AND (updated_at >= %s OR JSON_CONTAINS(activityStatus, '\"进行中\"'))
            ORDER BY activityPublishTime DESC
            """
            cursor.execute(activities_sql, (user_id, threshold_date))
            activities = cursor.fetchall()

            # 获取发布活动的参与者统计
            published_activity_ids = [activity['activityId'] for activity in activities]
            published_activity_stats = {}
            if published_activity_ids:
                # 构建IN子句的占位符
                placeholders = ','.join(['%s'] * len(published_activity_ids))
                stats_sql = f"""
                SELECT 
                    ap.activityId,
                    COUNT(*) as total_participants,
                    SUM(CASE 
                        WHEN ap.extraData IS NULL OR ap.extraData = '' OR ap.extraData = '{{}}' THEN 1
                        WHEN JSON_UNQUOTE(JSON_EXTRACT(ap.extraData, '$."审核状态"')) = '通过' THEN 1
                        WHEN JSON_EXTRACT(ap.extraData, '$."审核状态"') IS NULL THEN 1
                        ELSE 0
                    END) as approved_participants,
                    SUM(CASE 
                        WHEN JSON_UNQUOTE(JSON_EXTRACT(ap.extraData, '$."审核状态"')) = '申请中' THEN 1
                        ELSE 0
                    END) as pending_participants,
                    SUM(CASE 
                        WHEN JSON_UNQUOTE(JSON_EXTRACT(ap.extraData, '$."审核状态"')) = '未通过' THEN 1
                        ELSE 0
                    END) as rejected_participants
                FROM activity_participants ap
                WHERE ap.activityId IN ({placeholders})
                GROUP BY ap.activityId
                """
                
                cursor.execute(stats_sql, published_activity_ids)
                stats_results = cursor.fetchall()
                
                for stats in stats_results:
                    published_activity_stats[stats['activityId']] = {
                        'total_participants': stats['total_participants'],
                        'approved_participants': stats['approved_participants'], 
                        'pending_participants': stats['pending_participants'],
                        'rejected_participants': stats['rejected_participants']
                    }

            # 初始化两个活动列表
            ongoing_activities = []
            ended_activities = []

            # 处理活动的JSON字段并分类
            for activity in activities:
                if activity.get('activityImages'):
                    activity['activityImages'] = json.loads(activity['activityImages'])
                if activity.get('activityType'):
                    activity['activityType'] = json.loads(activity['activityType'])
                if activity.get('activityTags'):
                    activity['activityTags'] = json.loads(activity['activityTags'])
                if activity.get('activityCategory'):
                    activity['activityCategory'] = json.loads(activity['activityCategory'])
                if activity.get('activityRestriction'):
                    activity['activityRestriction'] = json.loads(activity['activityRestriction'])
                if activity.get('publisherTags'):
                    activity['publisherTags'] = json.loads(activity['publisherTags'])
                if activity.get('activityStatus'):
                    activity['activityStatus'] = json.loads(activity['activityStatus'])
                if activity.get('status'):
                    activity['status'] = json.loads(activity['status'])
                if activity.get('extraData'):
                    activity['extraData'] = json.loads(activity['extraData'])
                
                # 更新参与者统计信息
                activity_id = activity['activityId']
                if activity_id in published_activity_stats:
                    stats = published_activity_stats[activity_id]
                    activity['activityCurrentParticipants'] = stats['approved_participants']
                    activity['participantStats'] = {
                        'total': stats['total_participants'],
                        'approved': stats['approved_participants'],
                        'pending': stats['pending_participants'], 
                        'rejected': stats['rejected_participants']
                    }
                else:
                    activity['activityCurrentParticipants'] = 0
                    activity['participantStats'] = {
                        'total': 0,
                        'approved': 0,
                        'pending': 0,
                        'rejected': 0
                    }
                
                # 根据状态分类推送到不同的列表
                if activity.get('activityStatusMessage') == '活动进行中':
                    ongoing_activities.append(activity)
                else:  # '活动已结束'
                    ended_activities.append(activity)

            # 分别设置两个结果集
            result["activities"] = ongoing_activities
            result["ended_activities"] = ended_activities

            # 查询用户发布的评论
            comments_sql = """
            SELECT
                commentId, commentTitle, commentContent, commentImages,
                commentCategory, commentTags, commentLocation,
                ST_X(commentCoordinates) as longitude,
                ST_Y(commentCoordinates) as latitude,
                commentPublishTime, shoppingImages,
                publisherId, publisherAvatar, publisherNickname,
                publisherSignature, publisherTags, publisherPoints,
                likeCount, commentCount, shareCount,
                status, extraData, updated_at
            FROM comments
            WHERE publisherId = %s AND updated_at >= %s
            AND NOT JSON_CONTAINS(status, '"删除"')
            ORDER BY commentPublishTime DESC
            """
            cursor.execute(comments_sql, (user_id, threshold_date))
            comments = cursor.fetchall()

            # 处理JSON字段
            for comment in comments:
                # 处理 commentImages 字段
                if comment.get('commentImages'):
                    # 如果是字符串，尝试解析为JSON
                    if isinstance(comment['commentImages'], str):
                        try:
                            comment['commentImages'] = json.loads(comment['commentImages'])
                        except json.JSONDecodeError:
                            comment['commentImages'] = []
                    
                    # 检查是否是带有 values 键的对象
                    if isinstance(comment['commentImages'], dict) and 'values' in comment['commentImages']:
                        comment['commentImages'] = comment['commentImages']['values']
                
                # 处理 shoppingImages 字段
                if comment.get('shoppingImages'):
                    # 如果是字符串，尝试解析为JSON
                    if isinstance(comment['shoppingImages'], str):
                        try:
                            comment['shoppingImages'] = json.loads(comment['shoppingImages'])
                        except json.JSONDecodeError:
                            comment['shoppingImages'] = []
                    
                    # 检查是否是带有 values 键的对象
                    if isinstance(comment['shoppingImages'], dict) and 'values' in comment['shoppingImages']:
                        comment['shoppingImages'] = comment['shoppingImages']['values']
                
                # 处理其他JSON字段
                for field in ['commentTags', 'publisherTags', 'status', 'extraData', 'commentCategory']:
                    if comment.get(field):
                        if isinstance(comment[field], str):
                            try:
                                comment[field] = json.loads(comment[field])
                            except json.JSONDecodeError:
                                # 如果解析失败，设置为空列表或空对象
                                comment[field] = [] if field in ['commentTags', 'publisherTags', 'commentCategory'] else {}
                
                # 将距离转换为公里
                if comment.get('distance'):
                    comment['distance'] = round(comment['distance'] / 1000, 2)  # 转换为公里并保留两位小数

            result["comments"] = comments

            # 查询用户参与的活动并添加参与者extraData
            participated_activities_sql = """
            SELECT
                a.activityId, a.activityTitle, a.activityImages, a.activityType,
                a.activityTags, a.activityDetails, a.activityLocation,
                ST_X(a.activityCoordinates) as longitude,
                ST_Y(a.activityCoordinates) as latitude,
                a.activityPublishTime, a.activityCategory, a.activityRestriction,
                a.activityCurrentParticipants, a.activityMaxParticipants,
                a.publisherId, a.publisherAvatar, a.publisherNickname,
                a.publisherSignature, a.publisherTags, a.activityStatus,
                a.status, a.extraData,
                ap.joinTime, ap.participantStatus, ap.extraData as participantExtraData,
                CASE 
                    WHEN JSON_CONTAINS_PATH(a.extraData, 'one', '$.activityEndTime') 
                    AND STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(a.extraData, '$.activityEndTime')), '%%Y-%%m-%%d %%H:%%i') < NOW()
                    THEN '活动已结束'
                    ELSE '活动进行中'
                END as activityStatusMessage
            FROM activities a
            JOIN activity_participants ap ON a.activityId = ap.activityId
            WHERE ap.userId = %s
            AND NOT JSON_CONTAINS(a.activityStatus, '"删除"')
            ORDER BY ap.joinTime DESC
            """
            cursor.execute(participated_activities_sql, (user_id,))
            participated_activities = cursor.fetchall()

            # 获取所有活动ID，用于重新计算参与者统计
            activity_ids = [activity['activityId'] for activity in participated_activities]

            # 重新计算每个活动的真实参与者统计
            activity_stats = {}
            if activity_ids:
                # 构建IN子句的占位符
                placeholders = ','.join(['%s'] * len(activity_ids))
                stats_sql = f"""
                SELECT 
                    ap.activityId,
                    COUNT(*) as total_participants,
                    SUM(CASE 
                        WHEN ap.extraData IS NULL OR ap.extraData = '' OR ap.extraData = '{{}}' THEN 1
                        WHEN JSON_UNQUOTE(JSON_EXTRACT(ap.extraData, '$."审核状态"')) = '通过' THEN 1
                        WHEN JSON_EXTRACT(ap.extraData, '$."审核状态"') IS NULL THEN 1
                        ELSE 0
                    END) as approved_participants,
                    SUM(CASE 
                        WHEN JSON_UNQUOTE(JSON_EXTRACT(ap.extraData, '$."审核状态"')) = '申请中' THEN 1
                        ELSE 0
                    END) as pending_participants,
                    SUM(CASE 
                        WHEN JSON_UNQUOTE(JSON_EXTRACT(ap.extraData, '$."审核状态"')) = '未通过' THEN 1
                        ELSE 0
                    END) as rejected_participants
                FROM activity_participants ap
                WHERE ap.activityId IN ({placeholders})
                GROUP BY ap.activityId
                """
                
                cursor.execute(stats_sql, activity_ids)
                stats_results = cursor.fetchall()
                
                for stats in stats_results:
                    activity_stats[stats['activityId']] = {
                        'total_participants': stats['total_participants'],
                        'approved_participants': stats['approved_participants'], 
                        'pending_participants': stats['pending_participants'],
                        'rejected_participants': stats['rejected_participants']
                    }

            # 处理参与活动的JSON字段并添加统计信息
            for activity in participated_activities:
                # 处理JSON字段
                if activity.get('activityImages'):
                    activity['activityImages'] = json.loads(activity['activityImages'])
                if activity.get('activityType'):
                    activity['activityType'] = json.loads(activity['activityType'])
                if activity.get('activityTags'):
                    activity['activityTags'] = json.loads(activity['activityTags'])
                if activity.get('activityCategory'):
                    activity['activityCategory'] = json.loads(activity['activityCategory'])
                if activity.get('activityRestriction'):
                    activity['activityRestriction'] = json.loads(activity['activityRestriction'])
                if activity.get('publisherTags'):
                    activity['publisherTags'] = json.loads(activity['publisherTags'])
                if activity.get('activityStatus'):
                    activity['activityStatus'] = json.loads(activity['activityStatus'])
                if activity.get('status'):
                    activity['status'] = json.loads(activity['status'])
                if activity.get('extraData'):
                    activity['extraData'] = json.loads(activity['extraData'])
                
                # 处理参与者extraData
                if activity.get('participantExtraData'):
                    try:
                        activity['participantExtraData'] = json.loads(activity['participantExtraData'])
                    except:
                        activity['participantExtraData'] = {}
                
                # 添加真实的参与者统计信息
                activity_id = activity['activityId']
                if activity_id in activity_stats:
                    stats = activity_stats[activity_id]
                    # 更新为真实的通过审核的参与者人数
                    activity['activityCurrentParticipants'] = stats['approved_participants']
                    # 添加详细统计信息
                    activity['participantStats'] = {
                        'total': stats['total_participants'],
                        'approved': stats['approved_participants'],
                        'pending': stats['pending_participants'], 
                        'rejected': stats['rejected_participants']
                    }
                else:
                    # 如果没有统计数据，设置默认值
                    activity['activityCurrentParticipants'] = 0
                    activity['participantStats'] = {
                        'total': 0,
                        'approved': 0,
                        'pending': 0,
                        'rejected': 0
                    }

            result["participated_activities"] = participated_activities

            return result
    except Exception as e:
        # 打印更详细的错误信息，便于调试
        import traceback
        print(f"查询用户内容失败: {str(e)}")
        traceback.print_exc()
        return {"status": "error", "message": f"查询用户内容失败: {str(e)}"}
    finally:
        conn.close()







# 带过滤条件的用户内容查询路由
@app.post("/users/filtered_content/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_filtered_user_content(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("userId")
    content_type = data.get("contentType", ["users","activity"]) 
    filters = data.get("filters", [{"field": "activityStatus", "condition": ["进行中"]}])  # 过滤条件，格式为 [{"field": "字段名", "condition": ["值1", "值2"]}]

    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}

    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            result = {"status": "success"}
            
            # 处理用户信息查询
            if content_type == "all" or "users" in content_type:
                # 构建用户查询的基本SQL
                users_sql = """
                SELECT * FROM users WHERE userid = %s
                """
                
                # 执行查询
                cursor.execute(users_sql, (user_id,))
                user = cursor.fetchone()
                
                if user:
                    # 处理用户的JSON字段
                    json_fields = ["userData", "userTags", "userHobbies", "userImgUrls", "extraData"]
                    for field in json_fields:
                        if field in user and user[field]:
                            try:
                                user[field] = json.loads(user[field])
                            except (json.JSONDecodeError, TypeError):
                                # 如果解析失败，保持原样
                                pass
                
                result["user"] = user

            # 查询好友数（仅限已通过的好友申请）
            friends_sql = """
            SELECT COUNT(*) as friend_count 
            FROM friend_requests 
            WHERE (sender_id = %s OR receiver_id = %s) 
            AND status = 3
            """
            cursor.execute(friends_sql, (user_id, user_id))
            friend_result = cursor.fetchone()
            result["friend_count"] = friend_result['friend_count'] if friend_result else 0

            # 处理活动查询
            if content_type == "all" or "activity" in content_type:
                # 构建活动查询的基本SQL
                activities_sql = """
                SELECT
                    activityId, activityTitle, activityImages, activityType,
                    activityTags, activityDetails, activityLocation,
                    ST_X(activityCoordinates) as longitude,
                    ST_Y(activityCoordinates) as latitude,
                    activityPublishTime, activityCategory, activityRestriction,
                    activityCurrentParticipants, activityMaxParticipants,
                    publisherId, publisherAvatar, publisherNickname,
                    publisherSignature, publisherTags, activityStatus,
                    status, extraData, updated_at
                FROM activities
                WHERE publisherId = %s
                """
                
                # 添加过滤条件
                sql_params = [user_id]
                
                # 默认排除已删除的活动
                activities_sql += " AND NOT JSON_CONTAINS(activityStatus, '\"删除\"')"
                    # 添加过滤条件：排除已过期的活动
                # 添加过滤条件：排除已过期的活动
                activities_sql += """ 
                AND (
                    NOT JSON_CONTAINS_PATH(extraData, 'one', '$.activityEndTime') 
                    OR STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(extraData, '$.activityEndTime')), '%%Y-%%m-%%d %%H:%%i') >= NOW()
                )
                """
                # 处理自定义过滤条件
                for filter_item in filters:
                    field = filter_item.get("field")
                    condition = filter_item.get("condition", [])
                    
                    if field and condition:
                        # 对于JSON字段的特殊处理
                        if field in ["activityStatus", "activityType", "activityTags", "activityCategory"]:
                            # 构建OR条件组合
                            or_conditions = []
                            for value in condition:
                                or_conditions.append(f"JSON_CONTAINS({field}, %s)")
                                sql_params.append(json.dumps(value))
                            
                            if or_conditions:
                                activities_sql += f" AND ({' OR '.join(or_conditions)})"
                        else:
                            # 普通字段的处理
                            placeholders = ", ".join(["%s"] * len(condition))
                            activities_sql += f" AND {field} IN ({placeholders})"
                            sql_params.extend(condition)
                
                # 添加排序
                activities_sql += " ORDER BY activityPublishTime DESC"
                
                # 执行查询
                cursor.execute(activities_sql, sql_params)
                activities = cursor.fetchall()
                
                # 处理活动的JSON字段
                for activity in activities:
                    if activity.get('activityImages'):
                        activity['activityImages'] = json.loads(activity['activityImages'])
                    if activity.get('activityType'):
                        activity['activityType'] = json.loads(activity['activityType'])
                    if activity.get('activityTags'):
                        activity['activityTags'] = json.loads(activity['activityTags'])
                    if activity.get('activityCategory'):
                        activity['activityCategory'] = json.loads(activity['activityCategory'])
                    if activity.get('activityRestriction'):
                        activity['activityRestriction'] = json.loads(activity['activityRestriction'])
                    if activity.get('publisherTags'):
                        activity['publisherTags'] = json.loads(activity['publisherTags'])
                    if activity.get('activityStatus'):
                        activity['activityStatus'] = json.loads(activity['activityStatus'])
                    if activity.get('status'):
                        activity['status'] = json.loads(activity['status'])
                    if activity.get('extraData'):
                        activity['extraData'] = json.loads(activity['extraData'])
                
                result["activities"] = activities
            
            # 处理评论查询
            if content_type == "all" or content_type == "comment":
                # 构建评论查询的基本SQL
                comments_sql = """
                SELECT
                    commentId, commentTitle, commentContent, commentImages,
                    commentCategory, commentTags, commentLocation,
                    ST_X(commentCoordinates) as longitude,
                    ST_Y(commentCoordinates) as latitude,
                    commentPublishTime, shoppingImages,
                    publisherId, publisherAvatar, publisherNickname,
                    publisherSignature, publisherTags, publisherPoints,
                    likeCount, commentCount, shareCount,
                    status, extraData, updated_at
                FROM comments
                WHERE publisherId = %s
                """
                
                # 添加过滤条件
                sql_params = [user_id]
                
                # 默认排除已删除的评论
                comments_sql += " AND NOT JSON_CONTAINS(status, '\"删除\"')"
                
                # 处理自定义过滤条件
                for filter_item in filters:
                    field = filter_item.get("field")
                    condition = filter_item.get("condition", [])
                    
                    if field and condition:
                        # 对于JSON字段的特殊处理
                        if field in ["status", "commentCategory", "commentTags"]:
                            # 构建OR条件组合
                            or_conditions = []
                            for value in condition:
                                or_conditions.append(f"JSON_CONTAINS({field}, %s)")
                                sql_params.append(json.dumps(value))
                            
                            if or_conditions:
                                comments_sql += f" AND ({' OR '.join(or_conditions)})"
                        else:
                            # 普通字段的处理
                            placeholders = ", ".join(["%s"] * len(condition))
                            comments_sql += f" AND {field} IN ({placeholders})"
                            sql_params.extend(condition)
                
                # 添加排序
                comments_sql += " ORDER BY commentPublishTime DESC"
                
                # 执行查询
                cursor.execute(comments_sql, sql_params)
                comments = cursor.fetchall()
                
                # 处理评论的JSON字段
                for comment in comments:
                    if comment.get('commentImages'):
                        comment['commentImages'] = json.loads(comment['commentImages'])
                    if comment.get('commentCategory'):
                        comment['commentCategory'] = json.loads(comment['commentCategory'])
                    if comment.get('commentTags'):
                        comment['commentTags'] = json.loads(comment['commentTags'])
                    if comment.get('shoppingImages'):
                        comment['shoppingImages'] = json.loads(comment['shoppingImages'])
                    if comment.get('publisherTags'):
                        comment['publisherTags'] = json.loads(comment['publisherTags'])
                    if comment.get('status'):
                        comment['status'] = json.loads(comment['status'])
                    if comment.get('extraData'):
                        comment['extraData'] = json.loads(comment['extraData'])
                
                result["comments"] = comments
            
            # 处理参与活动查询
            if content_type == "all" or content_type == "participated_activity":
                # 构建参与活动查询的基本SQL
                participated_activities_sql = """
                SELECT
                    a.activityId, a.activityTitle, a.activityImages, a.activityType,
                    a.activityTags, a.activityDetails, a.activityLocation,
                    ST_X(a.activityCoordinates) as longitude,
                    ST_Y(a.activityCoordinates) as latitude,
                    a.activityPublishTime, a.activityCategory, a.activityRestriction,
                    a.activityCurrentParticipants, a.activityMaxParticipants,
                    a.publisherId, a.publisherAvatar, a.publisherNickname,
                    a.publisherSignature, a.publisherTags, a.activityStatus,
                    a.status, a.extraData,
                    ap.joinTime, ap.participantStatus
                FROM activities a
                JOIN activity_participants ap ON a.activityId = ap.activityId
                WHERE ap.userId = %s
                """
                
                # 添加过滤条件
                sql_params = [user_id]
                
                # 默认排除已删除的活动
                participated_activities_sql += " AND NOT JSON_CONTAINS(a.activityStatus, '\"删除\"')"
                
                # 处理自定义过滤条件
                for filter_item in filters:
                    field = filter_item.get("field")
                    condition = filter_item.get("condition", [])
                    
                    if field and condition:
                        # 对于JSON字段的特殊处理
                        if field in ["activityStatus", "activityType", "activityTags", "activityCategory"]:
                            # 构建OR条件组合
                            or_conditions = []
                            for value in condition:
                                or_conditions.append(f"JSON_CONTAINS(a.{field}, %s)")
                                sql_params.append(json.dumps(value))
                            
                            if or_conditions:
                                participated_activities_sql += f" AND ({' OR '.join(or_conditions)})"
                        # 处理参与者状态
                        elif field == "participantStatus":
                            placeholders = ", ".join(["%s"] * len(condition))
                            participated_activities_sql += f" AND ap.participantStatus IN ({placeholders})"
                            sql_params.extend(condition)
                        else:
                            # 普通字段的处理，注意添加表前缀
                            placeholders = ", ".join(["%s"] * len(condition))
                            participated_activities_sql += f" AND a.{field} IN ({placeholders})"
                            sql_params.extend(condition)
                
                # 添加排序
                participated_activities_sql += " ORDER BY ap.joinTime DESC"
                
                # 执行查询
                cursor.execute(participated_activities_sql, sql_params)
                participated_activities = cursor.fetchall()
                
                # 处理参与活动的JSON字段
                for activity in participated_activities:
                    if activity.get('activityImages'):
                        activity['activityImages'] = json.loads(activity['activityImages'])
                    if activity.get('activityType'):
                        activity['activityType'] = json.loads(activity['activityType'])
                    if activity.get('activityTags'):
                        activity['activityTags'] = json.loads(activity['activityTags'])
                    if activity.get('activityCategory'):
                        activity['activityCategory'] = json.loads(activity['activityCategory'])
                    if activity.get('activityRestriction'):
                        activity['activityRestriction'] = json.loads(activity['activityRestriction'])
                    if activity.get('publisherTags'):
                        activity['publisherTags'] = json.loads(activity['publisherTags'])
                    if activity.get('activityStatus'):
                        activity['activityStatus'] = json.loads(activity['activityStatus'])
                    if activity.get('status'):
                        activity['status'] = json.loads(activity['status'])
                    if activity.get('extraData'):
                        activity['extraData'] = json.loads(activity['extraData'])
                
                result["participated_activities"] = participated_activities
            
            return result
    except Exception as e:
        # 打印更详细的错误信息，便于调试
        import traceback
        print(f"查询用户过滤内容失败: {str(e)}")
        traceback.print_exc()
        return {"status": "error", "message": f"查询用户过滤内容失败: {str(e)}"}
    finally:
        conn.close()





# 根据评论ID获取评论详情
@app.get("/comments/{comment_id}")
async def get_comment_detail(comment_id: int):
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询评论详情
            sql = """
            SELECT 
                c.*,
                ST_X(c.commentCoordinates) as longitude, 
                ST_Y(c.commentCoordinates) as latitude,
                COUNT(cr.replyId) as replyCount
            FROM comments c
            LEFT JOIN comment_replies cr ON c.commentId = cr.commentId
            WHERE c.commentId = %s
            GROUP BY c.commentId
            """
            cursor.execute(sql, (comment_id,))
            comment = cursor.fetchone()
            
            if not comment:
                return {"status": "error", "message": "评论不存在或已删除"}
            
            # 处理JSON字段
            if comment.get('commentImages'):
                comment['commentImages'] = json.loads(comment['commentImages'])
            if comment.get('commentTags'):
                comment['commentTags'] = json.loads(comment['commentTags'])
            if comment.get('shoppingImages'):
                comment['shoppingImages'] = json.loads(comment['shoppingImages'])
            if comment.get('publisherTags'):
                comment['publisherTags'] = json.loads(comment['publisherTags'])
            if comment.get('extraData'):
                comment['extraData'] = json.loads(comment['extraData'])
            
            # 查询评论的回复
            replies_sql = """
            SELECT 
                replyId, commentId, replyContent, replyImages,
                publisherId, publisherAvatar, publisherNickname,
                replyTime, likeCount, parentReplyId, status, extraData
            FROM comment_replies
            WHERE commentId = %s
            ORDER BY 
                CASE WHEN parentReplyId IS NULL THEN replyId ELSE parentReplyId END,
                replyTime
            """
            cursor.execute(replies_sql, (comment_id,))
            replies = cursor.fetchall()
            
            # 处理回复的JSON字段
            for reply in replies:
                if reply.get('replyImages'):
                    reply['replyImages'] = json.loads(reply['replyImages'])
                if reply.get('extraData'):
                    reply['extraData'] = json.loads(reply['extraData'])
            
            # 将回复添加到评论中
            comment['replies'] = replies
            
            return {"status": "success", "data": comment}
    except Exception as e:
        return {"status": "error", "message": f"获取评论详情失败: {str(e)}"}
    finally:
        conn.close()




# 编辑活动
@app.post("/comments/get")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_comment_detail(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    comment_id = data.get("comment_id")
    
    if not comment_id:
        return {"status": "error", "message": "缺少评论ID参数"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询评论详情
            sql = """
            SELECT 
                c.*,
                ST_X(c.commentCoordinates) as longitude, 
                ST_Y(c.commentCoordinates) as latitude,
                COUNT(cr.replyId) as replyCount
            FROM comments c
            LEFT JOIN comment_replies cr ON c.commentId = cr.commentId
            WHERE c.commentId = %s
            GROUP BY c.commentId
            """
            cursor.execute(sql, (comment_id,))
            comment = cursor.fetchone()
            
            if not comment:
                return {"status": "error", "message": "评论不存在或已删除"}
            
            # 处理JSON字段
            if comment.get('commentImages'):
                comment['commentImages'] = json.loads(comment['commentImages'])
            if comment.get('commentTags'):
                comment['commentTags'] = json.loads(comment['commentTags'])
            if comment.get('shoppingImages'):
                comment['shoppingImages'] = json.loads(comment['shoppingImages'])
            if comment.get('publisherTags'):
                comment['publisherTags'] = json.loads(comment['publisherTags'])
            if comment.get('extraData'):
                comment['extraData'] = json.loads(comment['extraData'])
            
            # 查询评论的回复
            replies_sql = """
            SELECT 
                replyId, commentId, replyContent, replyImages,
                publisherId, publisherAvatar, publisherNickname,
                replyTime, likeCount, parentReplyId, status, extraData
            FROM comment_replies
            WHERE commentId = %s
            ORDER BY 
                CASE WHEN parentReplyId IS NULL THEN replyId ELSE parentReplyId END,
                replyTime
            """
            cursor.execute(replies_sql, (comment_id,))
            replies = cursor.fetchall()
            
            # 处理回复的JSON字段
            for reply in replies:
                if reply.get('replyImages'):
                    reply['replyImages'] = json.loads(reply['replyImages'])
                if reply.get('extraData'):
                    reply['extraData'] = json.loads(reply['extraData'])
            
            # 将回复添加到评论中
            comment['replies'] = replies
            
            return {"status": "success", "data": comment}
    except Exception as e:
        return {"status": "error", "message": f"获取评论详情失败: {str(e)}"}
    finally:
        conn.close()

# 根据用户ID获取用户发布的评论
@app.get("/users/{user_id}/comments")
async def get_user_comments(user_id: int):
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            sql = """
            SELECT 
                commentId, commentTitle, commentContent, commentImages, 
                commentCategory, commentTags, commentLocation, 
                ST_X(commentCoordinates) as longitude, 
                ST_Y(commentCoordinates) as latitude,
                commentPublishTime, shoppingImages,
                publisherId, publisherAvatar, publisherNickname,
                likeCount, commentCount, shareCount,
                status, extraData
            FROM comments
            WHERE publisherId = %s
            ORDER BY commentPublishTime DESC
            """
            cursor.execute(sql, (user_id,))
            comments = cursor.fetchall()
            
            # 处理JSON字段
            for comment in comments:
                if comment.get('commentImages'):
                    comment['commentImages'] = json.loads(comment['commentImages'])
                if comment.get('commentTags'):
                    comment['commentTags'] = json.loads(comment['commentTags'])
                if comment.get('shoppingImages'):
                    comment['shoppingImages'] = json.loads(comment['shoppingImages'])
                if comment.get('extraData'):
                    comment['extraData'] = json.loads(comment['extraData'])
            
            return {"status": "success", "data": comments}
    except Exception as e:
        return {"status": "error", "message": f"获取用户评论失败: {str(e)}"}
    finally:
        conn.close()


# 添加评论

@app.post("/comments/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def add_comment(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    conn = get_conn()
    actual_points_awarded = 0 # 初始化实际奖励的积分
    try:
        with conn.cursor() as cursor:
            # 准备默认值
            default_values = {
                "commentImages": '[]',
                "commentCategory": '["其他"]',
                "commentTags": '[]',
                "commentLocation": "",
                "shoppingImages": '[]',
                "publisherAvatar": "",
                "publisherSignature": "",
                "publisherTags": '[]',
                "publisherPoints": 0,
                "likeCount": 0,
                "commentCount": 0,
                "shareCount": 0,
                "status": '["开启"]',
                "extraData": '{}'
            }
            
            # 合并用户提供的数据和默认值
            for key, value in default_values.items():
                if key not in data or data[key] is None:
                    data[key] = value
            
            # 确保必填字段存在
            required_fields = [
                "commentTitle", "commentContent", "commentCategory", 
                "publisherId", "publisherNickname"
            ]
            for field in required_fields:
                if field not in data or not data[field]:
                    return {"status": "error", "message": f"缺少必填字段: {field}"}
            
            # 处理坐标
            longitude = data.get("longitude")
            latitude = data.get("latitude")
            if not longitude or not latitude:
                return {"status": "error", "message": "缺少经纬度信息"}
            
            # 确保JSON字段正确序列化
            for field in ["commentImages", "commentCategory", "commentTags", "shoppingImages", 
                         "publisherTags", "status", "extraData"]:
                if isinstance(data.get(field), (dict, list)):
                    data[field] = json.dumps(data[field])
                elif data.get(field) is None:
                    data[field] = '[]' if field in ["commentImages", "commentTags", "shoppingImages", "publisherTags"] else '{}'
            
            # 构建SQL语句
            sql = """
            INSERT INTO comments (
                commentTitle, commentContent, commentImages, commentCategory, 
                commentTags, commentLocation, commentCoordinates, shoppingImages,
                publisherId, publisherAvatar, publisherNickname, publisherSignature, 
                publisherTags, publisherPoints, likeCount, commentCount, 
                shareCount, status, extraData
            ) VALUES (
                %s, %s, %s, %s, %s, %s, ST_GeomFromText(%s), %s, %s, %s, 
                %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
            """
            
            point = f"POINT({longitude} {latitude})"
            params = (
                data["commentTitle"], 
                data["commentContent"], 
                data["commentImages"], 
                data["commentCategory"],
                data["commentTags"], 
                data["commentLocation"], 
                point,
                data["shoppingImages"],
                data["publisherId"], 
                data["publisherAvatar"], 
                data["publisherNickname"],
                data["publisherSignature"], 
                data["publisherTags"], 
                data["publisherPoints"],
                data["likeCount"], 
                data["commentCount"], 
                data["shareCount"],
                data["status"], 
                data["extraData"]
            )
            
            cursor.execute(sql, params)
            comment_id = cursor.lastrowid
            conn.commit() # 提交评论插入

            # --- 修改后的积分处理逻辑 ---
            try:
                publisher_id = data["publisherId"]
                points_to_add = 10

                cursor.execute("SELECT userPoints FROM users WHERE userid = %s", (publisher_id,))
                user_current_points_result = cursor.fetchone()
                
                points_before = 0
                if user_current_points_result:
                    points_before = user_current_points_result[0]
                else:
                    print(f"警告: 无法找到用户 {publisher_id} 的积分信息，将使用默认值0。")

                update_points_sql = "UPDATE users SET userPoints = userPoints + %s WHERE userid = %s"
                cursor.execute(update_points_sql, (points_to_add, publisher_id))
                
                points_after = points_before + points_to_add

                log_points_sql = """
                INSERT INTO user_points_log (
                    userid, points_change, points_before, points_after, 
                    change_type, change_reason, related_id, operator, status
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s
                )
                """
                log_params = (
                    publisher_id,
                    points_to_add,
                    points_before,
                    points_after,
                    3, # change_type (3: 活动奖励, 用于评论奖励)
                    "发布评论奖励积分",
                    str(comment_id),
                    "system",
                    "有效"
                )
                cursor.execute(log_points_sql, log_params)
                
                conn.commit() 
                actual_points_awarded = points_to_add # 积分成功添加
            except Exception as point_error:
                print(f"更新用户 {publisher_id} 积分或记录积分变动失败: {point_error}")
                # actual_points_awarded 保持为 0
            # --- 积分处理逻辑结束 ---

            store_name = None
            evaluation_type = None
            response_message = "评论添加成功"
            
            try:
                extra_data_parsed = json.loads(data["extraData"])
                if isinstance(extra_data_parsed, list) and len(extra_data_parsed) > 0:
                    for item in extra_data_parsed:
                        if isinstance(item, dict):
                            if "Store_name" in item:
                                store_name = item["Store_name"]
                            if "evaluation_type" in item:
                                evaluation_type = item["evaluation_type"]
                elif isinstance(extra_data_parsed, dict):
                    if "Store_name" in extra_data_parsed:
                        store_name = extra_data_parsed["Store_name"]
                    if "evaluation_type" in extra_data_parsed:
                        evaluation_type = extra_data_parsed["evaluation_type"]
            except (json.JSONDecodeError, TypeError):
                pass
            
            # 确保评价类型是整数
            if evaluation_type is not None:
                try:
                    evaluation_type = int(evaluation_type)
                except (ValueError, TypeError):
                    evaluation_type = None
            
            if store_name:
                shop_query = "SELECT shopId, shopCommentIds FROM shops WHERE shopName = %s"
                cursor.execute(shop_query, (store_name,))
                shop = cursor.fetchone()
                
                if shop:
                    shop_id, comment_ids_json = shop
                    try:
                        comment_ids = json.loads(comment_ids_json) if comment_ids_json else []
                    except (json.JSONDecodeError, TypeError):
                        comment_ids = []
                    
                    if not isinstance(comment_ids, list):
                        comment_ids = []
                    comment_ids.append(comment_id)
                    
                    # 构建更新SQL，根据评价类型增加好评或差评数
                    if evaluation_type == 0:  # 好评
                        update_shop_sql = """
                        UPDATE shops 
                        SET shopCommentIds = %s, 
                            shopCommentCount = shopCommentCount + 1,
                            shopGoodReviews = shopGoodReviews + 1
                        WHERE shopId = %s
                        """
                    elif evaluation_type == 1:  # 差评
                        update_shop_sql = """
                        UPDATE shops 
                        SET shopCommentIds = %s, 
                            shopCommentCount = shopCommentCount + 1,
                            shopBadReviews = shopBadReviews + 1
                        WHERE shopId = %s
                        """
                    else:  # 无评价类型
                        update_shop_sql = """
                        UPDATE shops 
                        SET shopCommentIds = %s, 
                            shopCommentCount = shopCommentCount + 1
                        WHERE shopId = %s
                        """
                    
                    cursor.execute(update_shop_sql, (json.dumps(comment_ids), shop_id))
                    conn.commit()
                else:
                    # 创建店铺时设置初始好评/差评值
                    good_reviews = 1 if evaluation_type == 0 else 0
                    bad_reviews = 1 if evaluation_type == 1 else 0
                    
                    default_shop_values = {
                        "shopType": "餐饮",
                        "shopDescription": f"由评论创建的店铺：{store_name}",
                        "shopImages": '[]',
                        "shopCategory": data["commentCategory"], 
                        "shopTags": '[]',
                        "shopLocation": data.get("commentLocation", ""),
                        "shopOpenTime": "未知",
                        "shopPhone": "",
                        "shopAddress": "",
                        "ownerId": data["publisherId"],
                        "ownerPhone": "",
                        "ownerName": data["publisherNickname"],
                        "shopCommentIds": json.dumps([comment_id]),
                        "shopActivityIds": '[]',
                        "shopLikeIds": '[]',
                        "shopCheckInIds": '[]',
                        "shopCommentCount": 1,
                        "shopGoodReviews": good_reviews,
                        "shopBadReviews": bad_reviews,
                        "status": '["开启"]',
                        "extraData": '{}'
                    }
                    
                    shop_fields = ["shopName"] + list(default_shop_values.keys())
                    shop_placeholders = ", ".join(["%s"] * len(shop_fields))
                    shop_fields_str = ", ".join(shop_fields)
                    
                    insert_shop_sql = f"""
                    INSERT INTO shops ({shop_fields_str}, shopCoordinates) 
                    VALUES ({shop_placeholders}, ST_GeomFromText(%s))
                    """
                    shop_params_list = [store_name] + list(default_shop_values.values()) + [point]
                    cursor.execute(insert_shop_sql, tuple(shop_params_list))
                    conn.commit()
            
            return {
                "status": "success", 
                "message": response_message, 
                "comment_id": comment_id,
                "pointsChange": actual_points_awarded # 返回积分变动情况
            }
    except Exception as e:
        if conn: # 确保conn存在才执行rollback
            conn.rollback()
        return {"status": "error", "message": f"添加评论失败: {str(e)}"}
    finally:
        if conn: # 确保conn存在才执行close
            conn.close()




# 修改评论
@app.post("/comments/edit")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def update_comment(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    comment_id = data.get("comment_id")
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 先检查评论是否存在
            check_sql = """
            SELECT publisherId FROM comments 
            WHERE commentId = %s
            """
            cursor.execute(check_sql, (comment_id,))
            comment = cursor.fetchone()
            
            if not comment:
                return {"status": "error", "message": "评论不存在或已删除"}
            
            # 检查是否是评论的发布者
            publisher_id = comment[0]
            if data.get("publisherId") and int(data["publisherId"]) != publisher_id:
                return {"status": "error", "message": "只有评论发布者才能修改评论"}
            
            # 构建更新语句
            update_fields = []
            params = []
            
            # 处理普通字段
            field_mapping = {
                "commentTitle": "commentTitle",
                "commentContent": "commentContent",
                "commentLocation": "commentLocation",
                "publisherAvatar": "publisherAvatar",
                "publisherNickname": "publisherNickname",
                "publisherSignature": "publisherSignature",
                "publisherPoints": "publisherPoints"
            }
            
            # 处理JSON字段
            json_fields = {
                "commentImages": "commentImages",
                "commentCategory": "commentCategory",
                "commentTags": "commentTags",
                "shoppingImages": "shoppingImages",
                "publisherTags": "publisherTags",
                "status": "status",
                "extraData": "extraData"
            }
            
            # 处理普通字段
            for client_field, db_field in field_mapping.items():
                if client_field in data:
                    update_fields.append(f"{db_field} = %s")
                    params.append(data[client_field])
            
            # 处理JSON字段
            for client_field, db_field in json_fields.items():
                if client_field in data:
                    update_fields.append(f"{db_field} = %s")
                    # 确保值是有效的JSON
                    if isinstance(data[client_field], str):
                        try:
                            json_value = json.loads(data[client_field])
                            params.append(json.dumps(json_value))
                        except:
                            params.append(json.dumps(data[client_field]))
                    else:
                        params.append(json.dumps(data[client_field]))
            
            # 处理坐标
            if "longitude" in data and "latitude" in data:
                longitude = data["longitude"]
                latitude = data["latitude"]
                update_fields.append("commentCoordinates = ST_GeomFromText(%s)")
                params.append(f"POINT({longitude} {latitude})")
            
            if not update_fields:
                return {"status": "error", "message": "没有提供要更新的字段"}
            
            # 构建完整的SQL语句
            params.append(comment_id)  # 添加WHERE条件的参数
            update_sql = f"UPDATE comments SET {', '.join(update_fields)} WHERE commentId = %s"
            
            cursor.execute(update_sql, params)
            conn.commit()
            
            return {"status": "success", "message": "评论更新成功"}
    except Exception as e:
        return {"status": "error", "message": f"更新评论失败: {str(e)}"}
    finally:
        conn.close()


@app.post("/comments/like")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def like_comment(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("userId")
    comment_id = data.get("commentId")
    like_type = data.get("type", 1)  # 默认为1(点赞)，可以是0(重置)，1(点赞)，2(点踩)

    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}

    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查评论是否存在，并获取发布者ID
            check_sql = "SELECT publisherId FROM comments WHERE commentId = %s"
            cursor.execute(check_sql, (comment_id,))
            comment_info = cursor.fetchone()
            if not comment_info:
                conn.rollback()
                return {"status": "error", "message": "评论不存在或已删除"}

            publisher_id = comment_info[0]

            # SQL for logging points change - defined once
            log_points_sql_query = """
            INSERT INTO user_points_log (
                userid, points_change, points_before, points_after,
                change_type, change_reason, related_id, operator,
                remark, status, extraData, created_at
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """
            change_type_for_like_actions = 6  # 积分变动类型：6-其他 (可根据业务调整)
            default_status_for_log = '有效'
            default_extraData_for_log = None # Or json.dumps({})

            # 检查用户是否已经对该评论有操作记录
            check_like_sql = "SELECT * FROM comment_likes WHERE commentId = %s AND userId = %s"
            cursor.execute(check_like_sql, (comment_id, user_id))
            existing_like = cursor.fetchone()

            points_change = 0
            action_type = ""
            message = ""
            action_taken = ""

            if existing_like:
                current_status = existing_like[4]  # status字段

                if like_type == 0:  # 重置状态
                    action_taken = "reset"
                    message = "状态已重置"
                    if current_status == 1: # 原本是点赞
                        points_change = -2
                        action_type = "取消点赞"
                        # 减少评论点赞数和发布者获得的点赞数
                        cursor.execute("UPDATE comments SET likeCount = GREATEST(likeCount - 1, 0) WHERE commentId = %s", (comment_id,))
                        cursor.execute("UPDATE users u JOIN comments c ON u.userid = c.publisherId SET u.userLikes = GREATEST(u.userLikes - 1, 0) WHERE c.commentId = %s", (comment_id,))
                    elif current_status == 2: # 原本是点踩
                        points_change = 2 # 取消点踩，积分加回来
                        action_type = "取消点踩"
                    
                    if current_status != 0 : # 只有在真实状态改变时才更新
                        cursor.execute("UPDATE comment_likes SET status = 0, updated_at = NOW() WHERE commentId = %s AND userId = %s", (comment_id, user_id))

                elif like_type == 1:  # 点赞
                    if current_status == 1:  # 已经是点赞状态，则取消点赞
                        points_change = -2
                        action_type = "取消点赞"
                        message = "取消点赞成功"
                        action_taken = "unlike"
                        cursor.execute("UPDATE comment_likes SET status = 0, updated_at = NOW() WHERE commentId = %s AND userId = %s", (comment_id, user_id))
                        cursor.execute("UPDATE comments SET likeCount = GREATEST(likeCount - 1, 0) WHERE commentId = %s", (comment_id,))
                        cursor.execute("UPDATE users u JOIN comments c ON u.userid = c.publisherId SET u.userLikes = GREATEST(u.userLikes - 1, 0) WHERE c.commentId = %s", (comment_id,))
                    elif current_status == 2:  # 当前是点踩状态，改为点赞
                        points_change = 4  # +2 (取消点踩) +2 (点赞)
                        action_type = "从点踩改为点赞"
                        message = "从点踩改为点赞成功"
                        action_taken = "like"
                        cursor.execute("UPDATE comment_likes SET status = 1, updated_at = NOW() WHERE commentId = %s AND userId = %s", (comment_id, user_id))
                        cursor.execute("UPDATE comments SET likeCount = likeCount + 1 WHERE commentId = %s", (comment_id,)) # 之前点踩未计入likeCount，现在点赞+1
                        cursor.execute("UPDATE users u JOIN comments c ON u.userid = c.publisherId SET u.userLikes = u.userLikes + 1 WHERE c.commentId = %s", (comment_id,))
                    else:  # current_status == 0，中立状态，改为点赞
                        points_change = 2
                        action_type = "点赞"
                        message = "点赞成功"
                        action_taken = "like"
                        cursor.execute("UPDATE comment_likes SET status = 1, updated_at = NOW() WHERE commentId = %s AND userId = %s", (comment_id, user_id))
                        cursor.execute("UPDATE comments SET likeCount = likeCount + 1 WHERE commentId = %s", (comment_id,))
                        cursor.execute("UPDATE users u JOIN comments c ON u.userid = c.publisherId SET u.userLikes = u.userLikes + 1 WHERE c.commentId = %s", (comment_id,))
                
                elif like_type == 2: # 点踩
                    if current_status == 2: # 已经是点踩状态，则取消点踩
                        points_change = 2 # 取消点踩，积分加回来
                        action_type = "取消点踩"
                        message = "取消点踩成功"
                        action_taken = "undislike"
                        cursor.execute("UPDATE comment_likes SET status = 0, updated_at = NOW() WHERE commentId = %s AND userId = %s", (comment_id, user_id))
                    elif current_status == 1: # 当前是点赞状态，改为点踩
                        points_change = -4 # -2 (取消点赞) -2 (点踩)
                        action_type = "从点赞改为点踩"
                        message = "从点赞改为点踩成功"
                        action_taken = "dislike"
                        cursor.execute("UPDATE comment_likes SET status = 2, updated_at = NOW() WHERE commentId = %s AND userId = %s", (comment_id, user_id))
                        cursor.execute("UPDATE comments SET likeCount = GREATEST(likeCount - 1, 0) WHERE commentId = %s", (comment_id,)) # 取消点赞，likeCount-1
                        cursor.execute("UPDATE users u JOIN comments c ON u.userid = c.publisherId SET u.userLikes = GREATEST(u.userLikes - 1, 0) WHERE c.commentId = %s", (comment_id,))
                    else: # current_status == 0，中立状态，改为点踩
                        points_change = -2
                        action_type = "点踩"
                        message = "点踩成功"
                        action_taken = "dislike"
                        cursor.execute("UPDATE comment_likes SET status = 2, updated_at = NOW() WHERE commentId = %s AND userId = %s", (comment_id, user_id))

                if points_change != 0:
                    description = f"用户 {user_id} 对评论 {comment_id} 执行了 '{action_type}' 操作, 积分变更 {points_change}"
                    
                    cursor.execute("SELECT userPoints FROM users WHERE userid = %s FOR UPDATE", (publisher_id,))
                    points_before_val = cursor.fetchone()[0]
                    points_after_val = points_before_val + points_change

                    cursor.execute("UPDATE users SET userPoints = %s WHERE userid = %s", (points_after_val, publisher_id))
                    
                    log_values = (
                        publisher_id, points_change, points_before_val, points_after_val,
                        change_type_for_like_actions, action_type, str(comment_id), str(user_id),
                        description, default_status_for_log, default_extraData_for_log
                    )
                    cursor.execute(log_points_sql_query, log_values)
                
                conn.commit()
                return {"status": "success", "message": message, "action": action_taken, "pointsChange": points_change}

            else: # 用户之前没有对该评论进行过任何操作 (existing_like is None)
                if like_type == 1: # 点赞
                    points_change = 2
                    action_type = "首次点赞"
                    message = "点赞成功"
                    action_taken = "like"
                    cursor.execute("INSERT INTO comment_likes (commentId, userId, status, created_at, updated_at) VALUES (%s, %s, 1, NOW(), NOW())", (comment_id, user_id))
                    cursor.execute("UPDATE comments SET likeCount = likeCount + 1 WHERE commentId = %s", (comment_id,))
                    cursor.execute("UPDATE users u JOIN comments c ON u.userid = c.publisherId SET u.userLikes = u.userLikes + 1 WHERE c.commentId = %s", (comment_id,))
                elif like_type == 2: # 点踩
                    points_change = -2
                    action_type = "首次点踩"
                    message = "点踩成功"
                    action_taken = "dislike"
                    cursor.execute("INSERT INTO comment_likes (commentId, userId, status, created_at, updated_at) VALUES (%s, %s, 2, NOW(), NOW())", (comment_id, user_id))
                    # 点踩不改变评论的 likeCount，但发布者积分会变动
                elif like_type == 0: # 重置 (首次操作无状态可重置，但API允许)
                    # No points change, no action type, message indicates nothing to reset
                    conn.commit() # Commit potential insert if logic was different, here it's a no-op for points
                    return {"status": "success", "message": "无先前操作可重置", "action": "reset", "pointsChange": 0}
                else: # 无效的 like_type
                    conn.rollback()
                    return {"status": "error", "message": "无效的操作类型"}

                if points_change != 0:
                    description = f"用户 {user_id} 对评论 {comment_id} 执行了 '{action_type}' 操作, 积分变更 {points_change}"
                    
                    cursor.execute("SELECT userPoints FROM users WHERE userid = %s FOR UPDATE", (publisher_id,))
                    points_before_val = cursor.fetchone()[0]
                    points_after_val = points_before_val + points_change

                    cursor.execute("UPDATE users SET userPoints = %s WHERE userid = %s", (points_after_val, publisher_id))
                    
                    log_values = (
                        publisher_id, points_change, points_before_val, points_after_val,
                        change_type_for_like_actions, action_type, str(comment_id), str(user_id),
                        description, default_status_for_log, default_extraData_for_log
                    )
                    cursor.execute(log_points_sql_query, log_values)

                conn.commit()
                return {"status": "success", "message": message, "action": action_taken, "pointsChange": points_change}

    except Exception as e:
        if conn:
            conn.rollback()
        return {"status": "error", "message": f"操作失败: {e}"}
    finally:
        if conn:
            conn.close()



#  查询用户对评论的点赞/点踩状态
@app.get("/comments/like_status")
async def get_comment_like_status(request: Request):
    data = await request.json()
    user_id = data.get("userId")
    comment_id = data.get("commentId")
    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查用户对评论的操作状态
            check_like_sql = "SELECT status FROM comment_likes WHERE commentId = %s AND userId = %s"
            cursor.execute(check_like_sql, (comment_id, user_id))
            result = cursor.fetchone()
            
            if result:
                status_value = result[0]
                if status_value == 1:
                    return {"status": "success", "liked": True, "disliked": False, "statusCode": 1}
                elif status_value == 2:
                    return {"status": "success", "liked": False, "disliked": True, "statusCode": 2}
                else:
                    return {"status": "success", "liked": False, "disliked": False, "statusCode": 0}
            else:
                return {"status": "success", "liked": False, "disliked": False, "statusCode": 0}
    except Exception as e:
        return {"status": "error", "message": f"查询失败: {str(e)}"}
    finally:
        conn.close()




# 删除评论（软删除）
@app.delete("/comments/{comment_id}")
async def delete_comment(comment_id: int, request: Request):
    data = await request.json()
    user_id = data.get("userId")
    
    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查评论是否存在
            check_sql = """
            SELECT publisherId FROM comments 
            WHERE commentId = %s
            """
            cursor.execute(check_sql, (comment_id,))
            comment = cursor.fetchone()
            
            if not comment:
                return {"status": "error", "message": "评论不存在或已删除"}
            
            # 检查是否是评论的发布者
            publisher_id = comment[0]
            if int(user_id) != publisher_id:
                return {"status": "error", "message": "只有评论发布者才能删除评论"}
            
            # 软删除评论
            update_sql = """
            UPDATE comments 
            SET status = '["删除"]' 
            WHERE commentId = %s
            """
            cursor.execute(update_sql, (comment_id,))
            
            # 软删除评论的所有回复
            update_replies_sql = """
            UPDATE comment_replies 
            SET status = '["删除"]' 
            WHERE commentId = %s
            """
            cursor.execute(update_replies_sql, (comment_id,))
            
            conn.commit()
            return {"status": "success", "message": "评论删除成功"}
    except Exception as e:
        return {"status": "error", "message": f"删除评论失败: {str(e)}"}
    finally:
        conn.close()

# 获取评论的回复
@app.get("/comments/{comment_id}/replies")
async def get_comment_replies(comment_id: int):
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            sql = """
            SELECT 
                replyId, commentId, replyContent, replyImages,
                publisherId, publisherAvatar, publisherNickname,
                replyTime, likeCount, parentReplyId, status, extraData
            FROM comment_replies
            WHERE commentId = %s
            ORDER BY 
                CASE WHEN parentReplyId IS NULL THEN replyId ELSE parentReplyId END,
                replyTime
            """
            cursor.execute(sql, (comment_id,))
            replies = cursor.fetchall()
            
            # 处理JSON字段
            for reply in replies:
                if reply.get('replyImages'):
                    reply['replyImages'] = json.loads(reply['replyImages'])
                if reply.get('extraData'):
                    reply['extraData'] = json.loads(reply['extraData'])
            
            return {"status": "success", "data": replies}
    except Exception as e:
        return {"status": "error", "message": f"获取评论回复失败: {str(e)}"}
    finally:
        conn.close()

# 添加评论回复
@app.post("/comments/replies")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def add_comment_reply(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    comment_id = data.get("comment_id")
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查评论是否存在
            check_sql = "SELECT * FROM comments WHERE commentId = %s"
            cursor.execute(check_sql, (comment_id,))
            if not cursor.fetchone():
                return {"status": "error", "message": "评论不存在或已删除"}
            
            # 准备默认值
            default_values = {
                "replyImages": '[]',
                "publisherAvatar": "",
                "likeCount": 0,
                "parentReplyId": None,
                "status": '[]',
                "extraData": '{}'
            }
            
            # 合并用户提供的数据和默认值
            for key, value in default_values.items():
                if key not in data or data[key] is None:
                    data[key] = value
            
            # 确保必填字段存在
            required_fields = ["replyContent", "publisherId", "publisherNickname"]
            for field in required_fields:
                if field not in data or not data[field]:
                    return {"status": "error", "message": f"缺少必填字段: {field}"}
            
            # 如果有父回复ID，检查父回复是否存在
            parent_reply_id = data.get("parentReplyId")
            if parent_reply_id:
                check_parent_sql = """
                SELECT * FROM comment_replies 
                WHERE replyId = %s AND commentId = %s
                """
                cursor.execute(check_parent_sql, (parent_reply_id, comment_id))
                if not cursor.fetchone():
                    return {"status": "error", "message": "父回复不存在或已删除"}
            
            # 构建SQL语句
            sql = """
            INSERT INTO comment_replies (
                commentId, replyContent, replyImages, publisherId,
                publisherAvatar, publisherNickname, likeCount,
                parentReplyId, status, extraData
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
            """
            
            # 准备参数
            params = (
                comment_id,
                data["replyContent"],
                data["replyImages"],
                data["publisherId"],
                data["publisherAvatar"],
                data["publisherNickname"],
                data["likeCount"],
                data["parentReplyId"],
                data["status"],
                data["extraData"]
            )
            
            cursor.execute(sql, params)
            
            # 更新评论的回复数
            update_comment_sql = """
            UPDATE comments 
            SET commentCount = commentCount + 1 
            WHERE commentId = %s
            """
            cursor.execute(update_comment_sql, (comment_id,))
            
            conn.commit()
            
            return {
                "status": "success", 
                "message": "回复添加成功", 
                "reply_id": cursor.lastrowid
            }
    except Exception as e:
        return {"status": "error", "message": f"添加回复失败: {str(e)}"}
    finally:
        conn.close()

# 修改评论回复
@app.put("/comments/replies/{reply_id}")
async def update_comment_reply(reply_id: int, request: Request):
    data = await request.json()
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查回复是否存在
            check_sql = """
            SELECT publisherId, commentId FROM comment_replies 
            WHERE replyId = %s
            """
            cursor.execute(check_sql, (reply_id,))
            reply = cursor.fetchone()
            
            if not reply:
                return {"status": "error", "message": "回复不存在或已删除"}
            
            # 检查是否是回复的发布者
            publisher_id, comment_id = reply
            if data.get("publisherId") and int(data["publisherId"]) != publisher_id:
                return {"status": "error", "message": "只有回复发布者才能修改回复"}
            
            # 构建更新语句
            update_fields = []
            params = []
            
            # 处理普通字段
            field_mapping = {
                "replyContent": "replyContent",
                "replyImages": "replyImages",
                "publisherAvatar": "publisherAvatar",
                "publisherNickname": "publisherNickname",
                "status": "status",
                "extraData": "extraData"
            }
            
            for client_field, db_field in field_mapping.items():
                if client_field in data:
                    update_fields.append(f"{db_field} = %s")
                    params.append(data[client_field])
            
            if not update_fields:
                return {"status": "error", "message": "没有提供要更新的字段"}
            
            # 构建完整的SQL语句
            params.append(reply_id)  # 添加WHERE条件的参数
            update_sql = f"UPDATE comment_replies SET {', '.join(update_fields)} WHERE replyId = %s"
            
            cursor.execute(update_sql, params)
            conn.commit()
            
            return {"status": "success", "message": "回复更新成功"}
    except Exception as e:
        return {"status": "error", "message": f"更新回复失败: {str(e)}"}
    finally:
        conn.close()

# 点赞评论回复
@app.post("/comments/replies/{reply_id}/like")
async def like_comment_reply(reply_id: int, request: Request):
    data = await request.json()
    user_id = data.get("userId")
    
    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查回复是否存在
            check_sql = "SELECT * FROM comment_replies WHERE replyId = %s"
            cursor.execute(check_sql, (reply_id,))
            if not cursor.fetchone():
                return {"status": "error", "message": "回复不存在或已删除"}
            
            # 更新点赞数
            update_sql = """
            UPDATE comment_replies 
            SET likeCount = likeCount + 1 
            WHERE replyId = %s
            """
            cursor.execute(update_sql, (reply_id,))
            
            # 更新发布者获得的点赞数
            update_publisher_sql = """
            UPDATE users u
            JOIN comment_replies cr ON u.userid = cr.publisherId
            SET u.userLikes = u.userLikes + 1
            WHERE cr.replyId = %s
            """
            cursor.execute(update_publisher_sql, (reply_id,))
            
            conn.commit()
            return {"status": "success", "message": "点赞成功"}
    except Exception as e:
        return {"status": "error", "message": f"点赞失败: {str(e)}"}
    finally:
        conn.close()

# 删除评论回复（软删除）
@app.delete("/comments/replies/{reply_id}")
async def delete_comment_reply(reply_id: int, request: Request):
    data = await request.json()
    user_id = data.get("userId")
    
    if not user_id:
        return {"status": "error", "message": "缺少用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查回复是否存在
            check_sql = """
            SELECT publisherId, commentId FROM comment_replies 
            WHERE replyId = %s
            """
            cursor.execute(check_sql, (reply_id,))
            reply = cursor.fetchone()
            
            if not reply:
                return {"status": "error", "message": "回复不存在或已删除"}
            
            # 检查是否是回复的发布者
            publisher_id, comment_id = reply
            if int(user_id) != publisher_id:
                return {"status": "error", "message": "只有回复发布者才能删除回复"}
            
            # 软删除回复
            update_sql = """
            UPDATE comment_replies 
            SET status = '["删除"]' 
            WHERE replyId = %s
            """
            cursor.execute(update_sql, (reply_id,))
            
            # 更新评论的回复数
            update_comment_sql = """
            UPDATE comments 
            SET commentCount = commentCount - 1 
            WHERE commentId = %s AND commentCount > 0
            """
            cursor.execute(update_comment_sql, (comment_id,))
            
            conn.commit()
            return {"status": "success", "message": "回复删除成功"}
    except Exception as e:
        return {"status": "error", "message": f"删除回复失败: {str(e)}"}
    finally:
        conn.close()





#好友相关的查询路由
# 好友关系相关路由

# 1. 根据id添加好友申请
@app.post("/friends/send_request/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def send_friend_request(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    sender_id = data.get("sender_id")
    receiver_id = data.get("receiver_id")
    message = data.get("message", "我想添加你为好友")
    source = data.get("source", "搜索") # 好友来源
    # 获取free参数，默认为0
    free = data.get("free", 0)
    
    if not sender_id or not receiver_id:
        return {"status": "error", "message": "请提供发送者ID和接收者ID"}
    
    if sender_id == receiver_id:
        return {"status": "error", "message": "不能添加自己为好友"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 检查发送者积分是否足够
            check_sender_points_sql = "SELECT userPoints FROM users WHERE userid = %s" # MODIFIED: id -> userid
            cursor.execute(check_sender_points_sql, (sender_id,))
            sender_user_data = cursor.fetchone()
            
            if not sender_user_data:
                return {"status": "error", "message": "发送者用户不存在"}
            
            sender_points_before = sender_user_data["userPoints"]
            # 根据free参数决定是否扣除积分
            points_cost_sender = 0 if free == 1 else 10  # 如果free=1则不扣除积分
            
            if sender_points_before < points_cost_sender:
                # 根据所需积分给出不同的提示信息
                if points_cost_sender > 0:
                    return {"status": "error", "message": f"您当前积分不足，发送好友申请需要{points_cost_sender}积分"}
                else:
                    return {"status": "error", "message": "您当前积分状态异常，无法发送好友申请"}

            # 检查接收者是否存在并获取其当前积分
            check_receiver_sql = "SELECT userPoints FROM users WHERE userid = %s" # MODIFIED: id -> userid
            cursor.execute(check_receiver_sql, (receiver_id,))
            receiver_user_data = cursor.fetchone()
            if not receiver_user_data:
                return {"status": "error", "message": "接收者用户不存在"}
            receiver_points_before = receiver_user_data["userPoints"]
            points_gain_receiver = 5 # 接收申请获得的积分
            
            # 检查是否已经是好友
            check_friendship_sql = """
            SELECT * FROM friendships 
            WHERE (user1_id = %s AND user2_id = %s) OR (user1_id = %s AND user2_id = %s)
            """
            cursor.execute(check_friendship_sql, (sender_id, receiver_id, receiver_id, sender_id))
            existing_friendship = cursor.fetchone()
            
            if existing_friendship:
                return {"status": "error", "message": "你们已经是好友了"}
            
            # 检查是否已经有来自同一发送者的待处理的好友申请 (status=1 表示待处理)
            check_request_sql = """
            SELECT * FROM friend_requests 
            WHERE sender_id = %s AND receiver_id = %s AND status = 1 
            """ # status = 1 (待处理), 2 (已接受), 3 (已拒绝), 4 (已过期), 5 (已撤销)
            cursor.execute(check_request_sql, (sender_id, receiver_id))
            existing_request = cursor.fetchone()
            
            if existing_request:
                return {"status": "error", "message": "已经发送过好友申请，请等待对方处理"}
            
            expired_at = (datetime.now() + timedelta(hours=24)).strftime("%Y-%m-%d %H:%M:%S")
            
            conn.begin()
            request_id = None # 初始化 request_id
            try:
                # 插入好友申请
                insert_request_sql = """
                INSERT INTO friend_requests 
                (sender_id, receiver_id, status, message, source, expired_at, created_at, updated_at) 
                VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())
                """
                cursor.execute(insert_request_sql, (sender_id, receiver_id, 1, message, source, expired_at))
                request_id = cursor.lastrowid # 获取好友申请的ID

                # 扣除发送者积分
                sender_points_after = sender_points_before - points_cost_sender
                update_sender_points_sql = "UPDATE users SET userPoints = %s WHERE userid = %s" # MODIFIED: id -> userid
                cursor.execute(update_sender_points_sql, (sender_points_after, sender_id))
                
                # 增加接收者积分
                receiver_points_after = receiver_points_before + points_gain_receiver
                update_receiver_points_sql = "UPDATE users SET userPoints = %s WHERE userid = %s" # MODIFIED: id -> userid
                cursor.execute(update_receiver_points_sql, (receiver_points_after, receiver_id))
                
                # 记录积分变动 - 发送者
                log_points_sql = """
                INSERT INTO user_points_log (
                    userid, points_change, points_before, points_after, 
                    change_type, change_reason, related_id, operator, status, created_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
                """
                cursor.execute(log_points_sql, (
                    sender_id, 
                    -points_cost_sender, 
                    sender_points_before,
                    sender_points_after,
                    6,  # 变动类型：6-其他
                    f"发送好友申请给用户ID:{receiver_id}", 
                    str(request_id), # 关联好友请求ID
                    str(sender_id), # 操作者是发送者本人
                    "有效"
                ))
                
                # 记录积分变动 - 接收者
                cursor.execute(log_points_sql, (
                    receiver_id, 
                    points_gain_receiver, 
                    receiver_points_before,
                    receiver_points_after,
                    6,  # 变动类型：6-其他
                    f"收到来自用户ID:{sender_id}的好友申请", 
                    str(request_id), # 关联好友请求ID
                    str(sender_id), # 操作者（触发者）是发送者
                    "有效"
                ))
                
                conn.commit()
                # 根据是否扣除积分生成不同的消息
                if points_cost_sender > 0:
                    message = f"好友申请已发送！已扣除{points_cost_sender}积分"
                else:
                    message = "好友申请已发送！"

                return {
                    "status": "success", 
                    "message": message, 
                    "request_id": request_id,
                    "pointsChange": -points_cost_sender # 返回发送者的积分变动
                }
            except Exception as e:
                conn.rollback()
                # raise e # 可以选择重新抛出异常或返回更友好的错误信息
                return {"status": "error", "message": f"好友申请事务处理失败: {str(e)}"}
    except Exception as e:
        return {"status": "error", "message": f"发送好友申请失败: {str(e)}"}
    finally:
        if conn:
            conn.close()

# 2. 根据id查询用户关系
@app.post("/friends/check_relationship/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def check_relationship(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user1_id = data.get("user1_id")
    user2_id = data.get("user2_id")
    
    if not user1_id or not user2_id:
        return {"status": "error", "message": "请提供两个用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 检查是否是好友
            check_friendship_sql = """
            SELECT * FROM friendships 
            WHERE (user1_id = %s AND user2_id = %s) OR (user1_id = %s AND user2_id = %s)
            """
            cursor.execute(check_friendship_sql, (user1_id, user2_id, user2_id, user1_id))
            friendship = cursor.fetchone()
            
            if friendship:
                # 确定关系状态
                status_map = {1: "正常好友", 2: "特别关注", 3: "黑名单"}
                status_text = status_map.get(friendship["status"], "未知状态")
                
                return {
                    "status": "success",
                    "relationship": "friends",
                    "details": {
                        "friendship_id": friendship["friendship_id"],
                        "status": friendship["status"],
                        "status_text": status_text,
                        "remark": friendship["remark"],
                        "group_id": friendship["group_id"],
                        "intimacy_score": friendship["intimacy_score"],
                        "last_interaction": friendship["last_interaction"],
                        "created_at": friendship["created_at"]
                    }
                }
            
            # 检查是否有待处理的好友申请
            check_request_sql = """
            SELECT * FROM friend_requests 
            WHERE ((sender_id = %s AND receiver_id = %s) OR (sender_id = %s AND receiver_id = %s))
            AND status = 1
            """
            cursor.execute(check_request_sql, (user1_id, user2_id, user2_id, user1_id))
            request_record = cursor.fetchone()
            
            if request_record:
                direction = "sent" if request_record["sender_id"] == int(user1_id) else "received"
                return {
                    "status": "success",
                    "relationship": "pending",
                    "direction": direction,
                    "details": {
                        "request_id": request_record["request_id"],
                        "message": request_record["message"],
                        "created_at": request_record["created_at"],
                        "expired_at": request_record["expired_at"]
                    }
                }
            
            # 没有关系
            return {
                "status": "success",
                "relationship": "none"
            }
    except Exception as e:
        return {"status": "error", "message": f"查询用户关系失败: {str(e)}"}
    finally:
        conn.close()


# 3. 根据id返回用户好友列表
@app.post("/friends/list/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_friend_list(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("user_id")
    group_id = data.get("group_id")  # 可选，按分组筛选
    status = data.get("status")      # 可选，按状态筛选
    
    if not user_id:
        return {"status": "error", "message": "请提供用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 构建基础查询
            query = """
            SELECT f.*, 
                   CASE 
                       WHEN f.user1_id = %s THEN f.user2_id 
                       ELSE f.user1_id 
                   END AS friend_id,
                   u.username, u.userAvatarUrl, u.userSignature, u.userTags, u.userPhone, u.extraData
            FROM friendships f
            JOIN users u ON (
                CASE 
                    WHEN f.user1_id = %s THEN u.userid = f.user2_id 
                    ELSE u.userid = f.user1_id 
                END
            )
            WHERE (f.user1_id = %s OR f.user2_id = %s)
            """
            params = [user_id, user_id, user_id, user_id]
            
            # 添加筛选条件
            if group_id is not None:
                query += " AND f.group_id = %s"
                params.append(group_id)
            
            if status is not None:
                query += " AND f.status = %s"
                params.append(status)
            
            # 按亲密度排序
            query += " ORDER BY f.intimacy_score DESC"
            
            cursor.execute(query, params)
            friends = cursor.fetchall()
            
            # 处理JSON字段
            for friend in friends:
                # 处理userTags字段
                if "userTags" in friend and friend["userTags"]:
                    try:
                        friend["userTags"] = json.loads(friend["userTags"])
                    except:
                        pass
                
                # 处理extraData字段并替换userPhone
                if "extraData" in friend and friend["extraData"]:
                    try:
                        extra_data = json.loads(friend["extraData"])
                        if "rongcloud_user_id" in extra_data:
                            friend["userPhone"] = extra_data["rongcloud_user_id"]
                    except:
                        pass
                
                # 从返回结果中移除extraData字段，保持原有API结构
                if "extraData" in friend:
                    del friend["extraData"]
            
            return {
                "status": "success",
                "count": len(friends),
                "data": friends
            }
    except Exception as e:
        return {"status": "error", "message": f"获取好友列表失败: {str(e)}"}
    finally:
        conn.close()


# 4. 处理好友申请
@app.post("/friends/handle_request/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def handle_friend_request(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    request_id = data.get("request_id")
    action = data.get("action")  # accept, reject, ignore
    remark = data.get("remark", "")  # 好友备注，接受时可用
    group_id = data.get("group_id", 1)  # 好友分组，接受时可用
    
    if not request_id or not action:
        return {"status": "error", "message": "请提供申请ID和操作类型"}
    
    if action not in ["accept", "reject", "ignore"]:
        return {"status": "error", "message": "操作类型无效，请使用accept、reject或ignore"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询申请记录
            check_sql = "SELECT * FROM friend_requests WHERE request_id = %s"
            cursor.execute(check_sql, (request_id,))
            request_record = cursor.fetchone()
            
            if not request_record:
                return {"status": "error", "message": "好友申请不存在"}
            
            if request_record["status"] != 1:
                return {"status": "error", "message": "该申请已被处理或已过期"}
            
            # 获取当前时间
            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            
            if action == "accept":
                # 更新申请状态为已接受
                update_sql = """
                UPDATE friend_requests 
                SET status = 3, processed_at = %s 
                WHERE request_id = %s
                """
                cursor.execute(update_sql, (now, request_id))
                
                # 创建好友关系
                insert_friendship_sql = """
                INSERT INTO friendships 
                (user1_id, user2_id, status, remark, group_id, intimacy_score, created_at) 
                VALUES (%s, %s, 1, %s, %s, 10, %s)
                """
                cursor.execute(
                    insert_friendship_sql, 
                    (request_record["sender_id"], request_record["receiver_id"], remark, group_id, now)
                )
                
                conn.commit()
                return {
                    "status": "success",
                    "message": "已接受好友申请",
                    "friendship_id": cursor.lastrowid
                }
            
            elif action == "reject":
                # 更新申请状态为已拒绝
                update_sql = """
                UPDATE friend_requests 
                SET status = 2, processed_at = %s 
                WHERE request_id = %s
                """
                cursor.execute(update_sql, (now, request_id))
                conn.commit()
                return {
                    "status": "success",
                    "message": "已拒绝好友申请"
                }
            
            elif action == "ignore":
                # 更新申请状态为已忽略/过期
                update_sql = """
                UPDATE friend_requests 
                SET status = 4, processed_at = %s 
                WHERE request_id = %s
                """
                cursor.execute(update_sql, (now, request_id))
                conn.commit()
                return {
                    "status": "success",
                    "message": "已忽略好友申请"
                }
    
    except Exception as e:
        return {"status": "error", "message": f"处理好友申请失败: {str(e)}"}
    finally:
        conn.close()

# 5. 修改好友关系
@app.post("/friends/update/")
async def update_friendship(request: Request):
    data = await request.json()
    friendship_id = data.get("friendship_id")
    
    if not friendship_id:
        return {"status": "error", "message": "请提供好友关系ID"}
    
    # 可更新的字段
    updatable_fields = ["status", "remark", "group_id", "intimacy_score"]
    update_data = {}
    
    # 收集要更新的字段
    for field in updatable_fields:
        if field in data:
            update_data[field] = data[field]
    
    if not update_data:
        return {"status": "error", "message": "未提供任何需要更新的字段"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 检查好友关系是否存在
            check_sql = "SELECT * FROM friendships WHERE friendship_id = %s"
            cursor.execute(check_sql, (friendship_id,))
            friendship = cursor.fetchone()
            
            if not friendship:
                return {"status": "error", "message": "好友关系不存在"}
            
            # 构建更新SQL
            update_fields = []
            update_values = []
            
            for key, value in update_data.items():
                update_fields.append(f"{key} = %s")
                update_values.append(value)
            
            # 添加最后一个参数：friendship_id
            update_values.append(friendship_id)
            
            update_sql = f"""
            UPDATE friendships 
            SET {', '.join(update_fields)}, 
                last_interaction = CURRENT_TIMESTAMP
            WHERE friendship_id = %s
            """
            
            cursor.execute(update_sql, update_values)
            conn.commit()
            
            # 获取更新后的数据
            cursor.execute(check_sql, (friendship_id,))
            updated_friendship = cursor.fetchone()
            
            return {
                "status": "success",
                "message": "好友关系更新成功",
                "data": updated_friendship
            }
    
    except Exception as e:
        return {"status": "error", "message": f"更新好友关系失败: {str(e)}"}
    finally:
        conn.close()




# 6. 删除好友
@app.post("/friends/delete/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def delete_friendship(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("user_id")
    friend_id = data.get("friend_id")
    
    if not user_id or not friend_id:
        return {"status": "error", "message": "请提供用户ID和好友ID"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查找好友关系
            check_sql = """
            SELECT * FROM friendships 
            WHERE (user1_id = %s AND user2_id = %s) OR (user1_id = %s AND user2_id = %s)
            """
            cursor.execute(check_sql, (user_id, friend_id, friend_id, user_id))
            friendship = cursor.fetchone()
            
            if not friendship:
                return {"status": "error", "message": "好友关系不存在"}
            
            # 删除好友关系
            delete_sql = "DELETE FROM friendships WHERE friendship_id = %s"
            cursor.execute(delete_sql, (friendship["friendship_id"],))
            conn.commit()
            
            return {
                "status": "success",
                "message": "好友关系已删除"
            }
    
    except Exception as e:
        return {"status": "error", "message": f"删除好友关系失败: {str(e)}"}
    finally:
        conn.close()


# 7. 获取好友申请列表
@app.post("/friends/request_list/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_friend_requests(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据
    user_id = data.get("user_id")
    direction = data.get("direction", "received")  # received 或 sent
    status = data.get("status")  # 可选，按状态筛选
    
    if not user_id:
        return {"status": "error", "message": "请提供用户ID"}
    
    if direction not in ["received", "sent"]:
        return {"status": "error", "message": "direction参数无效，请使用received或sent"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 构建基础查询
            if direction == "received":
                query = """
                SELECT fr.*, 
                       u.userid, u.username, u.userPhone, u.userBirthday, u.userAvatarUrl, u.userSignature, u.userGender, 
                       u.userAge, u.userCity, u.userTags, u.userHobbies, u.userLevel
                FROM friend_requests fr
                JOIN users u ON fr.sender_id = u.userid
                WHERE fr.receiver_id = %s
                """
                params = [user_id]
            else:  # sent
                query = """
                SELECT fr.*, 
                       u.userid, u.username, u.userPhone, u.userBirthday, u.userAvatarUrl, u.userSignature, u.userGender, 
                       u.userAge, u.userCity, u.userTags, u.userHobbies, u.userLevel
                FROM friend_requests fr
                JOIN users u ON fr.receiver_id = u.userid
                WHERE fr.sender_id = %s
                """
                params = [user_id]
            
            # 添加状态筛选
            if status is not None:
                query += " AND fr.status = %s"
                params.append(status)
            
            # 按时间排序
            query += " ORDER BY fr.created_at DESC"
            
            cursor.execute(query, params)
            requests = cursor.fetchall()
            
            return {
                "status": "success",
                "count": len(requests),
                "data": requests
            }
    
    except Exception as e:
        return {"status": "error", "message": f"获取好友申请列表失败: {str(e)}"}
    finally:
        conn.close()

# 8. 获取好友分组统计
@app.post("/friends/group_stats/")
async def get_friend_group_stats(request: Request):
    data = await request.json()
    user_id = data.get("user_id")
    
    if not user_id:
        return {"status": "error", "message": "请提供用户ID"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询各分组的好友数量
            query = """
            SELECT group_id, COUNT(*) as count
            FROM friendships
            WHERE (user1_id = %s OR user2_id = %s) AND status = 1
            GROUP BY group_id
            ORDER BY group_id
            """
            cursor.execute(query, (user_id, user_id))
            group_stats = cursor.fetchall()
            
            # 查询总好友数
            total_query = """
            SELECT COUNT(*) as total
            FROM friendships
            WHERE (user1_id = %s OR user2_id = %s) AND status = 1
            """
            cursor.execute(total_query, (user_id, user_id))
            total = cursor.fetchone()["total"]
            
            # 查询特别关注数量
            special_query = """
            SELECT COUNT(*) as special_count
            FROM friendships
            WHERE (user1_id = %s OR user2_id = %s) AND status = 2
            """
            cursor.execute(special_query, (user_id, user_id))
            special_count = cursor.fetchone()["special_count"]
            
            # 查询黑名单数量
            blacklist_query = """
            SELECT COUNT(*) as blacklist_count
            FROM friendships
            WHERE (user1_id = %s OR user2_id = %s) AND status = 3
            """
            cursor.execute(blacklist_query, (user_id, user_id))
            blacklist_count = cursor.fetchone()["blacklist_count"]
            
            return {
                "status": "success",
                "total_friends": total,
                "special_attention": special_count,
                "blacklist": blacklist_count,
                "groups": group_stats
            }
    
    except Exception as e:
        return {"status": "error", "message": f"获取好友分组统计失败: {str(e)}"}
    finally:
        conn.close()



@app.post("/reports/query_by_user/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def query_reports_by_user(request: Request):
    """
    根据用户ID查询其提交的举报内容。
    支持分页。
    """
    try:
        data = get_decrypted_data(request)  # 获取解密后的数据
        reporter_id = data.get("reporterId")
        page = int(data.get("page", 1))
        page_size = int(data.get("page_size", 10))

        if not reporter_id:
            return JSONResponse(
                status_code=400,
                content={"status": "error", "message": "缺少举报者ID (reporterId)"}
            )

        offset = (page - 1) * page_size
        conn = get_conn()
        cursor = conn.cursor(pymysql.cursors.DictCursor)

        try:
            # 查询总记录数
            count_sql = """
            SELECT COUNT(*) as total 
            FROM reports 
            WHERE reporterId = %s
            """
            cursor.execute(count_sql, (reporter_id,))
            total_count_result = cursor.fetchone()
            total_count = total_count_result["total"] if total_count_result else 0
            total_pages = (total_count + page_size - 1) // page_size

            # 查询举报记录
            query_sql = """
            SELECT 
                reportId, reportType, targetId, reportReason, reportImages,
                reportCategory, reportTags, reportLocation, 
                DATE_FORMAT(reportTime, '%%Y-%%m-%%d %%H:%%i:%%s') as reportTime, 
                reporterId, reporterUsername, reporterAvatar,
                targetUserId, targetUsername, targetAvatar,
                processStatus, processResult, 
                DATE_FORMAT(processTime, '%%Y-%%m-%%d %%H:%%i:%%s') as processTime, 
                processorId, status, extraData,
                DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H:%%i:%%s') as created_at,
                DATE_FORMAT(updated_at, '%%Y-%%m-%%d %%H:%%i:%%s') as updated_at
            FROM reports
            WHERE reporterId = %s
            ORDER BY reportTime DESC
            LIMIT %s OFFSET %s
            """
            cursor.execute(query_sql, (reporter_id, page_size, offset))
            reports = cursor.fetchall()

            # 处理JSON字段，将JSON字符串转换为Python对象
            json_fields_to_parse = ["reportImages", "reportCategory", "reportTags", "status", "extraData"]
            for report in reports:
                for field in json_fields_to_parse:
                    if field in report and report[field]:
                        try:
                            report[field] = json.loads(report[field])
                        except (json.JSONDecodeError, TypeError):
                            # 如果解析失败，可以根据需要设置为None或保持原样，或者一个空结构
                            report[field] = None # 或者 report[field] = {} 或 []

            return {
                "status": "success",
                "data": {
                    "reports": reports,
                    "pagination": {
                        "total": total_count,
                        "page": page,
                        "page_size": page_size,
                        "total_pages": total_pages
                    }
                }
            }

        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    except Exception as e:
        logger.error(f"查询用户举报内容时出错: {str(e)}")
        logger.error(traceback.format_exc()) # 记录详细错误堆栈
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": f"查询举报内容失败: {str(e)}"}
        )




@app.post("/reports/create/")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def create_report(request: Request):
    data = get_decrypted_data(request)  # 获取解密后的数据  
    report_type = data.get("reportType")
    reporter_id = data.get("reporterId")
    target_user_id = data.get("targetUserId")
    content_id = data.get("contentId")
    report_reason = data.get("reportReason")
    
    # 验证必填字段
    if not report_type:
        return {"status": "error", "message": "请提供举报类型"}
    
    if not reporter_id:
        return {"status": "error", "message": "请提供举报者ID"}
    
    if not report_reason:
        return {"status": "error", "message": "请提供举报内容"}
    
    # 验证至少有一个：被举报的ID或内容ID
    if not target_user_id and not content_id:
        return {"status": "error", "message": "请至少提供被举报的ID或内容ID"}
    
    conn = get_conn()
    try:
        with conn.cursor(cursors.DictCursor) as cursor:
            # 获取举报者信息
            reporter_sql = "SELECT username, userAvatarUrl, userPhone FROM users WHERE userid = %s"
            cursor.execute(reporter_sql, (reporter_id,))
            reporter = cursor.fetchone()
            
            if not reporter:
                return {"status": "error", "message": "举报者不存在"}
            
            # 如果有被举报用户ID，获取被举报用户信息
            target_user = None
            if target_user_id:
                target_sql = "SELECT username, userAvatarUrl FROM users WHERE userid = %s"
                cursor.execute(target_sql, (target_user_id,))
                target_user = cursor.fetchone()
                
                if not target_user:
                    return {"status": "error", "message": "被举报用户不存在"}
            
            # 设置默认值和从请求中获取的值
            report_data = {
                "reportType": report_type,
                "targetId": content_id if content_id else target_user_id,  # 如果有内容ID则使用内容ID，否则使用用户ID
                "reportReason": report_reason,
                "reportImages": data.get("reportImages", []),
                "reportCategory": data.get("reportCategory", {}),
                "reportTags": data.get("reportTags", []),
                "reportLocation": data.get("reportLocation", "未知"),
                "reporterId": reporter_id,
                "reporterPhone": reporter["userPhone"],
                "reporterUsername": reporter["username"],
                "reporterAvatar": reporter["userAvatarUrl"],
                "targetUserId": target_user_id,
                "processStatus": 0,  # 默认未处理
                "status": data.get("status", {}),
                "extraData": data.get("extraData", {})
            }
            
            # 如果有被举报用户，添加用户信息
            if target_user:
                report_data["targetUsername"] = target_user["username"]
                report_data["targetAvatar"] = target_user["userAvatarUrl"]
            
            # 处理JSON字段
            json_fields = ["reportImages", "reportCategory", "reportTags", "status", "extraData"]
            for field in json_fields:
                if field in report_data:
                    # 确保值是JSON字符串
                    if isinstance(report_data[field], str):
                        try:
                            # 尝试解析JSON字符串
                            json_obj = json.loads(report_data[field])
                            report_data[field] = json.dumps(json_obj, ensure_ascii=False)
                        except json.JSONDecodeError:
                            # 如果不是有效的JSON字符串，则将其作为普通字符串处理
                            report_data[field] = json.dumps(report_data[field], ensure_ascii=False)
                    else:
                        # 如果是Python对象，直接转换为JSON字符串
                        report_data[field] = json.dumps(report_data[field], ensure_ascii=False)
            
            # 构建SQL插入语句
            fields = list(report_data.keys())
            placeholders = ", ".join(["%s"] * len(fields))
            field_str = ", ".join(fields)
            
            insert_sql = f"INSERT INTO reports ({field_str}) VALUES ({placeholders})"
            
            # 构建参数值
            values = list(report_data.values())
            
            # 执行插入
            cursor.execute(insert_sql, values)
            conn.commit()
            
            # 获取新创建的举报信息
            report_id = cursor.lastrowid
            get_report_sql = "SELECT * FROM reports WHERE reportId = %s"
            cursor.execute(get_report_sql, (report_id,))
            new_report = cursor.fetchone()
            
            # 处理JSON字段，将JSON字符串转换为Python对象
            if new_report:
                for field in json_fields:
                    if field in new_report and new_report[field]:
                        try:
                            new_report[field] = json.loads(new_report[field])
                        except (json.JSONDecodeError, TypeError):
                            # 如果解析失败，保持原样
                            pass
            
            return {
                "status": "success", 
                "message": "举报提交成功", 
                "report_id": report_id,
                "data": new_report
            }
    except Exception as e:
        return {"status": "error", "message": f"提交举报失败: {str(e)}"}
    finally:
        conn.close()





# 积分相关路由
# ... existing code ...

# 增加/减少用户积分
@app.post("/user/points/change")
async def change_user_points(request: Request):
    data = await request.json()
    user_id = data.get("userid")
    points_change = data.get("points_change")  # 正数为增加，负数为减少
    change_type = data.get("change_type", 6)  # 变动类型：1-签到，2-购物，3-活动奖励，4-积分兑换，5-管理员调整，6-其他
    
    # 准备默认值
    default_values = {
        "change_reason": "",
        "related_id": "",
        "operator": "system",
        "remark": "",
        "status": "有效",
        "extraData": '{}'
    }
    
    # 合并用户提供的数据和默认值
    for key, value in default_values.items():
        if key not in data or data[key] is None:
            data[key] = value
    
    # 确保必填字段存在
    required_fields = ["userid", "points_change", "change_type"]
    for field in required_fields:
        if field not in data or data[field] is None:
            return {"status": "error", "message": f"缺少必填字段: {field}"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 检查用户是否存在
            check_user_sql = "SELECT userid, userPoints FROM users WHERE userid = %s"
            cursor.execute(check_user_sql, (user_id,))
            user = cursor.fetchone()
            
            if not user:
                return {"status": "error", "message": "用户不存在"}
            
            # 获取当前积分
            current_points = user["userPoints"]
            
            # 计算变动后积分
            new_points = current_points + points_change
            
            # 如果积分变为负数，返回错误
            if new_points < 0:
                return {"status": "error", "message": "积分不足，无法扣减"}
            
            # 更新用户积分
            update_user_sql = "UPDATE users SET userPoints = %s WHERE userid = %s"
            cursor.execute(update_user_sql, (new_points, user_id))
            
            # 记录积分变动日志
            log_sql = """
            INSERT INTO user_points_log (
                userid, points_change, points_before, points_after, 
                change_type, change_reason, related_id, operator, 
                remark, status, extraData
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
            """
            
            log_params = (
                user_id,
                points_change,
                current_points,
                new_points,
                change_type,
                data["change_reason"],
                data["related_id"],
                data["operator"],
                data["remark"],
                data["status"],
                data["extraData"]
            )
            
            cursor.execute(log_sql, log_params)
            conn.commit()
            
            return {
                "status": "success",
                "message": "积分更新成功",
                "data": {
                    "userid": user_id,
                    "points_before": current_points,
                    "points_after": new_points,
                    "points_change": points_change,
                    "log_id": cursor.lastrowid
                }
            }
    except Exception as e:
        conn.rollback()
        return {"status": "error", "message": f"积分更新失败: {str(e)}"}
    finally:
        conn.close()

# 查询用户当前积分
@app.get("/user/points/current")
async def get_user_current_points(request: Request):
    # 从查询参数获取用户ID
    params = request.query_params
    user_id = params.get("userid")
    
    if not user_id:
        return {"status": "error", "message": "缺少用户ID参数"}
    
    conn = get_conn()
    try:
        with conn.cursor() as cursor:
            # 查询用户积分
            query_sql = """
            SELECT userid, username, userPoints, userLevel, 
                   created_at, updated_at 
            FROM users 
            WHERE userid = %s
            """
            cursor.execute(query_sql, (user_id,))
            user = cursor.fetchone()
            
            if not user:
                return {"status": "error", "message": "用户不存在"}
            
            return {
                "status": "success",
                "data": {
                    "userid": user["userid"],
                    "username": user["username"],
                    "points": user["userPoints"],
                    "level": user["userLevel"],
                    "created_at": user["created_at"],
                    "updated_at": user["updated_at"]
                }
            }
    except Exception as e:
        return {"status": "error", "message": f"查询积分失败: {str(e)}"}
    finally:
        conn.close()

# 查询用户积分变更历史
# 查询用户积分变更历史
# 查询用户积分变更历史
@app.post("/user/points/history")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_user_points_history(request: Request):
    try:
        # 从请求体获取参数，而不是查询参数
        data = get_decrypted_data(request)  # 获取解密后的数据
        
        user_id = data.get("userid")
        page = int(data.get("page", 1))
        page_size = int(data.get("page_size", 10))
        change_type = data.get("change_type")  # 可选的变动类型过滤，默认为None表示全部
        
        if not user_id:
            return {"status": "error", "message": "缺少用户ID参数"}
        
        # 计算偏移量
        offset = (page - 1) * page_size
        
        conn = get_conn()
        try:
            with conn.cursor(cursors.DictCursor) as cursor:  # 使用DictCursor确保结果以字典形式返回
                # 先查询用户当前总积分
                cursor.execute("SELECT userPoints FROM users WHERE userid = %s", [user_id])
                user_data = cursor.fetchone()
                if not user_data:
                    return {"status": "error", "message": "用户不存在"}
                
                current_points = user_data["userPoints"] or 0
                
                # 构建基础查询
                base_query = """
                SELECT id, userid, points_change, points_before, points_after, 
                       change_type, change_reason, related_id, operator, 
                       remark, status, created_at
                FROM user_points_log 
                WHERE userid = %s
                """
                
                count_query = "SELECT COUNT(*) as total FROM user_points_log WHERE userid = %s"
                
                params = [user_id]
                
                # 添加变动类型过滤条件 (只有在明确指定时才过滤)
                if change_type is not None:
                    base_query += " AND change_type = %s"
                    count_query += " AND change_type = %s"
                    params.append(change_type)
                
                # 添加排序和分页
                base_query += " ORDER BY created_at DESC LIMIT %s OFFSET %s"
                
                # 执行计数查询
                cursor.execute(count_query, params)
                total_result = cursor.fetchone()
                total = total_result["total"] if total_result else 0
                
                # 执行分页查询
                cursor.execute(base_query, params + [page_size, offset])
                records = cursor.fetchall()
                
                # 处理结果，确保datetime对象被格式化为字符串
                history = []
                for record in records:
                    history_item = {
                        "id": record["id"],
                        "userid": record["userid"],
                        "points_change": record["points_change"],
                        "points_before": record["points_before"],
                        "points_after": record["points_after"],
                        "change_type": record["change_type"],
                        "change_reason": record["change_reason"],
                        "related_id": record["related_id"],
                        "operator": record["operator"],
                        "remark": record["remark"],
                        "status": record["status"],
                        # 格式化日期时间为字符串
                        "created_at": record["created_at"].strftime("%Y-%m-%d %H:%M:%S") if record["created_at"] else None
                    }
                    history.append(history_item)
                
                return {
                    "status": "success",
                    "data": {
                        "total": total,
                        "page": page,
                        "page_size": page_size,
                        "records": history,
                        "current_points": current_points  # 添加当前积分到返回数据
                    }
                }
        except Exception as e:
            import traceback
            error_details = traceback.format_exc()
            return {"status": "error", "message": f"查询积分历史失败: {str(e)}", "details": error_details}
        finally:
            conn.close()
    except Exception as e:
        import traceback
        error_details = traceback.format_exc()
        return {"status": "error", "message": f"查询积分历史失败: {str(e)}", "details": error_details}



# === Alipay API Routes ===

# 获取支付宝配置
def get_alipay_config_from_db(request=None):
    """
    从数据库获取支付宝配置信息
    """
    conn = get_conn()
    cursor = conn.cursor()
    try:
        # 获取当前环境下的支付宝配置
        cursor.execute("""
            SELECT config_name, config_value 
            FROM payment_config 
            WHERE platform = 'alipay' AND is_active = 1 AND environment = 'dev'
        """)
        
        config_data = cursor.fetchall()
        alipay_config = {}
        
        for config_name, config_value in config_data:
            alipay_config[config_name] = config_value
        
        # 格式化私钥 - 添加PEM头尾和换行
        if 'app_private_key' in alipay_config:
            # 移除可能已存在的头尾
            private_key = alipay_config['app_private_key'].strip()
            if not private_key.startswith('-----BEGIN RSA PRIVATE KEY-----'):
                # 添加PEM头尾并格式化为多行
                formatted_key = "-----BEGIN RSA PRIVATE KEY-----\n"
                # 每64个字符添加一个换行
                key_chars = [private_key[i:i+64] for i in range(0, len(private_key), 64)]
                formatted_key += "\n".join(key_chars)
                formatted_key += "\n-----END RSA PRIVATE KEY-----"
                alipay_config['app_private_key'] = formatted_key
        
        # 格式化公钥 - 添加PEM头尾和换行
        if 'alipay_public_key' in alipay_config:
            # 移除可能已存在的头尾
            public_key = alipay_config['alipay_public_key'].strip()
            if not public_key.startswith('-----BEGIN PUBLIC KEY-----'):
                # 添加PEM头尾并格式化为多行
                formatted_key = "-----BEGIN PUBLIC KEY-----\n"
                # 每64个字符添加一个换行
                key_chars = [public_key[i:i+64] for i in range(0, len(public_key), 64)]
                formatted_key += "\n".join(key_chars)
                formatted_key += "\n-----END PUBLIC KEY-----"
                alipay_config['alipay_public_key'] = formatted_key
                
        # 设置API基础URL，如果request参数有效则使用请求URL构建，否则使用默认
        if request:
            base_url = request.url.scheme + "://" + request.url.netloc
            # 如果数据库中没有设置notify_url和return_url，则使用默认值
            if 'notify_url' not in alipay_config or not alipay_config['notify_url'] or alipay_config['notify_url'].startswith('https://example.com'):
                alipay_config['notify_url'] = f"{base_url}/api/payment/alipay/notify/"
            
            if 'return_url' not in alipay_config or not alipay_config['return_url'] or alipay_config['return_url'].startswith('https://example.com'):
                alipay_config['return_url'] = f"{base_url}/api/payment/alipay/return/"
        
        # 检查必要的配置是否存在
        required_fields = ['app_id', 'app_private_key', 'alipay_public_key']
        for field in required_fields:
            if field not in alipay_config:
                raise ValueError(f"缺少必要的支付宝配置: {field}")
        
        # 添加签名类型
        alipay_config['sign_type'] = "RSA2"
        
        # 日志输出核对密钥格式是否正确（只输出前后10个字符）
        logger.info(f"应用私钥格式: {alipay_config['app_private_key'][:30]}...{alipay_config['app_private_key'][-30:]}")
        logger.info(f"支付宝公钥格式: {alipay_config['alipay_public_key'][:30]}...{alipay_config['alipay_public_key'][-30:]}")
        
        return alipay_config
    
    except Exception as e:
        logger.error(f"获取支付宝配置出错: {str(e)}")
        raise
    finally:
        cursor.close()
        conn.close()






@app.post("/api/payment/alipay/create_app_order", summary="创建支付宝App支付订单")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def create_alipay_app_order(
    request_data: Request
):
    """
    创建支付宝APP支付订单。
    客户端应发送必要的订单信息，例如用户ID、商品ID/套餐ID，用于确定金额和商品描述。
    """
    try:
        data = get_decrypted_data(request_data)  # 获取解密后的数据
        user_id = data.get("user_id")
        package_id = data.get("package_id") # 积分套餐ID
        
        if not all([user_id, package_id]):
            raise HTTPException(status_code=400, detail="缺少必要的订单参数: user_id, package_id")
            
        # 从数据库获取积分套餐的详细信息
        conn = get_conn()
        cursor = conn.cursor(cursors.DictCursor)
        try:
            cursor.execute(
                """
                SELECT package_id, package_name, points_amount, price, discount, original_price
                FROM points_package
                WHERE package_id = %s AND is_active = 1
                """, 
                (package_id,)
            )
            package_info = cursor.fetchone()
            
            if not package_info:
                raise HTTPException(status_code=400, detail=f"找不到积分套餐或套餐已停用: {package_id}")
                
            order_amount_str = str(package_info['price']) # 金额字符串
            order_subject = f"{package_info['package_name']} ({package_info['points_amount']}积分)"
            points_amount = package_info['points_amount']
            
        except Exception as db_e:
            logger.error(f"查询积分套餐信息出错: {db_e}")
            raise HTTPException(status_code=500, detail=f"查询积分套餐失败: {str(db_e)}")
        finally:
            cursor.close()

        # 1. 生成唯一的商户订单号(out_trade_no)
        now = datetime.now()
        out_trade_no = now.strftime("%Y%m%d%H%M%S") + str(user_id) + str(random.randint(1000, 9999))

        # 2. 在数据库中创建订单记录
        try:
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT INTO payment_orders 
                (order_id, user_id, amount, points, order_title, payment_platform, payment_status, 
                alipay_out_trade_no, expire_time, extraData) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, 
                (out_trade_no, user_id, order_amount_str, points_amount, order_subject, 'alipay', 0, out_trade_no, 
                 (now + timedelta(minutes=30)).strftime("%Y-%m-%d %H:%M:%S"),
                 json.dumps({"package_id": package_id}))
            )
            conn.commit()
            logger.info(f"为用户 {user_id} 在数据库中创建订单 {out_trade_no}, 金额: {order_amount_str}, 积分: {points_amount}")
        except Exception as db_e:
            conn.rollback()
            logger.error(f"在数据库中创建订单 {out_trade_no} 出错: {db_e}")
            raise HTTPException(status_code=500, detail=f"在数据库中创建订单失败: {str(db_e)}")
        finally:
            cursor.close()
            conn.close()

        # 3. 使用python-alipay-sdk创建支付订单
        from alipay import AliPay
        from alipay.utils import AliPayConfig
        
        # 获取支付宝配置
        alipay_config = get_alipay_config_from_db(request_data)
        
        # 创建AliPay实例
        alipay = AliPay(
            appid=alipay_config['app_id'],
            app_notify_url=alipay_config['notify_url'],  # 异步通知URL
            app_private_key_string=alipay_config['app_private_key'],  # 应用私钥
            alipay_public_key_string=alipay_config['alipay_public_key'],  # 支付宝公钥
            sign_type=alipay_config['sign_type'],  # 签名类型，RSA2
            debug=True,  # 沙箱模式
            config=AliPayConfig(timeout=15)  # 可选配置
        )
        
        # 调用接口生成支付参数
        order_string = alipay.api_alipay_trade_app_pay(
            out_trade_no=out_trade_no,  # 订单号
            total_amount=order_amount_str,  # 订单金额
            subject=order_subject,  # 商品名称
            return_url=alipay_config['return_url'],  # 支付成功后跳转URL
            notify_url=alipay_config['notify_url']  # 异步通知URL
        )
        
        logger.info(f"为订单 {out_trade_no} 生成支付宝订单字符串: {order_string[:100]}...") # 记录片段
        
        return JSONResponse(content={
            "status": "success",
            "out_trade_no": out_trade_no,
            "alipay_order_string": order_string,
            "message": "支付宝订单创建成功。请在应用中使用此订单字符串。"
        })

    except HTTPException as he:
        logger.warning(f"create_alipay_app_order中的HTTP异常: {he.detail}")
        raise he
    except ValueError as ve:
        logger.error(f"create_alipay_app_order中的值错误: {str(ve)}")
        return JSONResponse(status_code=400, content={"status": "error", "error": str(ve)})
    except Exception as e:
        logger.error(f"创建支付宝App订单时出错: {str(e)}")
        logger.error(traceback.format_exc())
        return JSONResponse(status_code=500, content={"status": "error", "error": "创建支付宝订单时发生内部服务器错误"})





# 获取积分套餐列表
@app.post("/api/points/packages/", summary="获取积分套餐列表")
async def get_points_packages(request: Request):
    """
    获取所有可用的积分套餐列表
    """
    try:
        conn = get_conn()
        with conn.cursor(cursors.DictCursor) as cursor:
            # 查询所有激活状态的套餐，按排序顺序和价格排序
            sql = """
            SELECT 
                package_id,
                package_name,
                points_amount,
                price,
                discount,
                original_price,
                package_description,
                is_popular,
                is_active,
                sort_order,
                icon_url,
                extraData
            FROM points_package 
            WHERE is_active = 1
            ORDER BY sort_order ASC, price ASC
            """
            cursor.execute(sql)
            packages = cursor.fetchall()
            
            # 处理每个套餐的数据
            for package in packages:
                # 处理JSON字段
                if package.get('extraData'):
                    try:
                        package['extraData'] = json.loads(package['extraData'])
                    except json.JSONDecodeError:
                        package['extraData'] = {}
                
                # 计算折扣百分比
                if package['original_price'] and package['original_price'] > 0:
                    package['discount_percentage'] = round(
                        (1 - package['price'] / package['original_price']) * 100
                    )
                else:
                    package['discount_percentage'] = 0
            
            return {
                "status": "success",
                "data": packages
            }
            
    except Exception as e:
        logger.error(f"获取积分套餐列表时出错: {str(e)}")
        logger.error(traceback.format_exc())
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": "获取积分套餐列表失败"}
        )
    finally:
        conn.close()


@app.post("/api/payment/alipay/notify/", summary="支付宝异步通知回调")
async def alipay_notify_callback(request: Request):
    """
    处理支付宝的异步通知回调。
    支付宝会发送POST请求到这个接口，通知支付结果。
    """
    try:
        # 获取POST表单数据
        post_data = await request.form()
        notify_data = dict(post_data) 
        logger.info(f"收到支付宝异步通知 (原始数据): {notify_data}")
        
        # 获取支付宝配置
        alipay_config = get_alipay_config_from_db(request)
        
        logger.info(f"使用的支付宝配置: app_id={alipay_config['app_id']}")
        
        # 提取签名
        signature = notify_data.get('sign')
        if not signature:
            logger.warning("支付宝通知缺少签名字段")
            return "fail"
        
        # 准备验签数据，移除sign和sign_type
        data_to_verify = {}
        for key, value in notify_data.items():
            if key != 'sign' and key != 'sign_type' and value:
                data_to_verify[key] = value
        
        # 按字典升序排序并构建待验证内容字符串
        ordered_items = sorted(data_to_verify.items())
        content_to_verify = "&".join([f"{k}={v}" for k, v in ordered_items])
        
        logger.info(f"手动构造的待验签字符串: {content_to_verify}")
        
        # 直接使用底层验签函数
        from alipay.aop.api.util.SignatureUtils import verify_with_rsa
        
        try:
            success = verify_with_rsa(
                alipay_public_key=alipay_config['alipay_public_key'],
                source_content=content_to_verify.encode('utf-8'),
                signature=signature,
                char_set='utf-8',
                sign_type='RSA2'
            )
            
            logger.info(f"手动验签结果: {success}")
            
            if not success:
                logger.warning(f"支付宝通知验签失败，将尝试使用SDK方法")
                # 保留SDK验证方法作为备用
                from alipay import AliPay
                from alipay.utils import AliPayConfig
                
                alipay = AliPay(
                    appid=alipay_config['app_id'],
                    app_notify_url=alipay_config['notify_url'],
                    app_private_key_string=alipay_config['app_private_key'],
                    alipay_public_key_string=alipay_config['alipay_public_key'],
                    sign_type=alipay_config['sign_type'],
                    debug=True,
                    config=AliPayConfig(timeout=15)
                )
                
                # 复制处理用的数据
                sdk_notify_data = notify_data.copy()
                sdk_signature = sdk_notify_data.pop('sign', None)
                sdk_notify_data.pop('sign_type', None)
                
                try:
                    sdk_success = alipay.verify(sdk_notify_data, sdk_signature)
                    logger.info(f"SDK验签结果: {sdk_success}")
                    success = success or sdk_success  # 任一方法成功即可
                except Exception as sdk_e:
                    logger.error(f"SDK验签发生异常: {str(sdk_e)}")
                
                if not success:
                    # 最后尝试：直接通过该通知进行业务处理
                    # 在生产环境中，这通常不建议，但在调试阶段可以先处理订单
                    logger.warning("两种验签方法均失败，但将继续处理通知")
                    # 您可以根据业务需求决定是否继续处理
                    # 如果继续处理，请移除下面这行
                    # return "fail"
        except Exception as e:
            logger.error(f"验签过程中发生异常: {str(e)}")
            import traceback
            logger.error(traceback.format_exc())
            # 在调试阶段可以决定是否仍然处理订单
            # return "fail"
        
        # 处理订单 - 到这里说明验签成功或我们决定继续处理
        if notify_data.get("trade_status") not in ("TRADE_SUCCESS", "TRADE_FINISHED"):
            logger.info(f"支付宝通知交易状态不是成功状态: {notify_data.get('trade_status')}")
            return "success"  # 返回success表示收到，但不处理
            
        # 获取交易号
        out_trade_no = notify_data.get("out_trade_no")
        trade_no = notify_data.get("trade_no")
        total_amount = notify_data.get("total_amount")
        
        logger.info(f"处理支付宝订单: out_trade_no={out_trade_no}, trade_no={trade_no}, total_amount={total_amount}")
        
        # 以下是订单处理逻辑，与验签无关，保持原样
        import pymysql.cursors
        conn = None
        cursor = None
        
        try:
            conn = get_conn()
            conn.autocommit(False)  # 关闭自动提交，开启事务
            cursor = conn.cursor(pymysql.cursors.DictCursor)
            
            # 1. 查询订单信息
            cursor.execute(
                """
                SELECT payment_status, user_id, points, amount, order_title 
                FROM payment_orders 
                WHERE order_id = %s
                """, 
                (out_trade_no,)
            )
            order_data = cursor.fetchone()
            
            # 如果订单不存在
            if not order_data:
                logger.warning(f"找不到订单: {out_trade_no}")
                return "fail"
                
            # 如果订单已处理
            if order_data['payment_status'] != 0:
                logger.info(f"订单 {out_trade_no} 已处理，当前状态: {order_data['payment_status']}")
                return "success"
                
            # 检查金额是否匹配（可选）
            db_amount = str(float(order_data['amount']))
            if db_amount != total_amount:
                logger.warning(f"订单金额不匹配: 数据库金额 {db_amount}, 支付宝通知金额 {total_amount}")
                # 可以选择继续处理或返回失败，这里选择继续处理
            
            # 2. 更新订单状态
            current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cursor.execute(
                """
                UPDATE payment_orders 
                SET payment_status = 1, 
                    alipay_trade_no = %s, 
                    payment_time = %s, 
                    notify_data = %s,
                    alipay_app_id = %s
                WHERE order_id = %s AND payment_status = 0
                """, 
                (trade_no, current_time, json.dumps(notify_data), alipay_config['app_id'], out_trade_no)
            )
            
            if cursor.rowcount <= 0:
                logger.warning(f"更新订单状态失败，可能已被其他进程处理: {out_trade_no}")
                conn.rollback()
                return "success"  # 返回成功，避免重复通知
            
            logger.info(f"订单 {out_trade_no} 支付成功，已更新状态")
            
            # 3. 查询用户当前积分
            user_id = order_data['user_id']
            points_to_add = order_data['points']
            
            cursor.execute(
                """
                SELECT userPoints FROM users WHERE userid = %s
                """, 
                (user_id,)
            )
            user_data = cursor.fetchone()
            
            if not user_data:
                logger.error(f"找不到用户: {user_id}")
                conn.rollback()
                return "success"  # 返回成功，避免重复通知
                
            current_points = user_data['userPoints'] if user_data['userPoints'] is not None else 0
            new_points = current_points + points_to_add
            
            # 4. 更新用户积分
            cursor.execute(
                """
                UPDATE users 
                SET userPoints = %s 
                WHERE userid = %s
                """, 
                (new_points, user_id)
            )
            
            if cursor.rowcount <= 0:
                logger.error(f"更新用户积分失败: {user_id}")
                conn.rollback()
                return "success"  # 返回成功，避免重复通知
            
            # 5. 记录积分变动历史
            try:
                cursor.execute(
                    """
                    INSERT INTO user_points_log 
                    (userid, points_change, points_before, points_after, change_type, 
                    change_reason, related_id, operator, status, created_at) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """, 
                    (user_id, points_to_add, current_points, new_points, 2, 
                     f'购买积分套餐: {order_data["order_title"]}', out_trade_no, 'system', 
                     '有效', current_time)
                )
            except Exception as log_error:
                # 如果记录积分历史失败，记录错误但继续提交事务
                logger.error(f"记录积分变动历史失败: {str(log_error)}")
                import traceback
                logger.error(traceback.format_exc())
            
            # 6. 提交事务
            conn.commit()
            logger.info(f"用户 {user_id} 积分增加 {points_to_add}，从 {current_points} 到 {new_points}")
            
            return "success"
        
        except Exception as e:
            if conn:
                try:
                    conn.rollback()
                except:
                    pass
            logger.error(f"处理支付宝通知时数据库操作出错: {str(e)}")
            import traceback
            logger.error(traceback.format_exc())
            return "fail"
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                try:
                    conn.close()
                except:
                    pass
    except Exception as e:
        logger.error(f"处理支付宝异步通知时出错: {str(e)}")
        import traceback
        logger.error(traceback.format_exc())
        return "fail"



@app.post("/api/payment/records/", summary="查询用户充值记录")
@require_encryption(["timestamp"])  # 添加解密装饰器
async def get_payment_records(request: Request):
    """
    查询用户充值记录，只返回充值成功的记录。
    
    参数:
    - userid: 用户ID
    - page: 页码，默认为1
    - page_size: 每页记录数，默认为10
    """
    try:
        # 获取请求参数
        data = get_decrypted_data(request)  # 获取解密后的数据
        user_id = data.get("userid")
        page = int(data.get("page", 1))
        page_size = int(data.get("page_size", 10))
        
        # 参数验证
        if not user_id:
            return JSONResponse(
                status_code=400,
                content={"status": "error", "message": "用户ID不能为空"}
            )
        
        # 计算分页偏移量
        offset = (page - 1) * page_size
        
        conn = get_conn()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        try:
            # 先查询用户当前积分
            user_points_sql = """
            SELECT userPoints 
            FROM users 
            WHERE userid = %s
            """
            cursor.execute(user_points_sql, (user_id,))
            user_data = cursor.fetchone()
            current_points = user_data["userPoints"] if user_data and user_data["userPoints"] is not None else 0
            
            # 查询总记录数
            count_sql = """
            SELECT COUNT(*) as total 
            FROM payment_orders 
            WHERE user_id = %s AND payment_status = 1
            """
            cursor.execute(count_sql, (user_id,))
            total_count = cursor.fetchone()["total"]
            total_pages = (total_count + page_size - 1) // page_size
            
            # 查询记录
            query_sql = """
            SELECT 
                order_id, user_id, amount, points, order_title, 
                payment_platform, alipay_trade_no, payment_status,
                DATE_FORMAT(payment_time, '%%Y-%%m-%%d %%H:%%i:%%s') as payment_time, 
                DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H:%%i:%%s') as created_at
            FROM payment_orders 
            WHERE user_id = %s AND payment_status = 1
            ORDER BY payment_time DESC, created_at DESC
            LIMIT %s OFFSET %s
            """
            cursor.execute(query_sql, (user_id, page_size, offset))
            records = cursor.fetchall()
            
            # 处理JSON字段
            for record in records:
                # 使用安全的方式处理可能的JSON字段
                try:
                    if "notify_data" in record and record["notify_data"]:
                        record["notify_data"] = json.loads(record["notify_data"])
                except (json.JSONDecodeError, TypeError):
                    record["notify_data"] = {}
                
                try:
                    if "extraData" in record and record["extraData"]:
                        record["extraData"] = json.loads(record["extraData"])
                except (json.JSONDecodeError, TypeError):
                    record["extraData"] = {}
            
            # 返回响应
            return {
                "status": "success",
                "data": {
                    "user_points": current_points,  # 添加用户当前积分
                    "records": records,
                    "pagination": {
                        "total": total_count,
                        "page": page,
                        "page_size": page_size,
                        "total_pages": total_pages
                    }
                }
            }
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"查询充值记录时出错: {str(e)}")
        logger.error(traceback.format_exc())
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": f"查询充值记录失败: {str(e)}"}
        )



@app.get("/api/payment/alipay/return/", summary="支付宝同步跳转URL")
async def alipay_return_url(request: Request):
    """
    处理支付宝同步跳转。
    用户支付完成后，如果是在网页端进行的支付，支付宝会将用户重定向到这个URL。
    """
    try:
        # 获取GET参数
        params = dict(request.query_params)
        logger.info(f"收到支付宝同步跳转: {params}")
        
        if not params:
            return HTMLResponse("无法获取支付宝返回参数")
        
        # 获取支付宝配置
        alipay_config = get_alipay_config_from_db(request)
        
        # 创建AliPay实例
        from alipay import AliPay
        from alipay.utils import AliPayConfig
        import pymysql.cursors
        
        alipay = AliPay(
            appid=alipay_config['app_id'],
            app_notify_url=alipay_config['notify_url'],
            app_private_key_string=alipay_config['app_private_key'],
            alipay_public_key_string=alipay_config['alipay_public_key'],
            sign_type=alipay_config['sign_type'],
            debug=True,
            config=AliPayConfig(timeout=15)
        )
        
        # 验证参数的真实性
        verify_result = alipay.verify(params)
        
        # 获取订单信息
        out_trade_no = params.get("out_trade_no", "")
        trade_no = params.get("trade_no", "")
        total_amount = params.get("total_amount", "")
        
        # 查询订单信息
        order_data = None
        user_data = None
        conn = None
        cursor = None
        
        try:
            conn = get_conn()
            cursor = conn.cursor(pymysql.cursors.DictCursor)
            
            # 1. 查询订单信息
            cursor.execute(
                """
                SELECT o.user_id, o.points, o.amount, o.order_title, o.payment_status, 
                       o.payment_time, o.created_at
                FROM payment_orders o
                WHERE o.order_id = %s
                """, 
                (out_trade_no,)
            )
            order_data = cursor.fetchone()
            
            if order_data:
                # 2. 查询用户信息
                cursor.execute(
                    """
                    SELECT u.username, u.userPhone, u.userPoints
                    FROM users u
                    WHERE u.userid = %s
                    """, 
                    (order_data['user_id'],)
                )
                user_data = cursor.fetchone()
                
                # 3. 根据支付状态显示文本
                if order_data['payment_status'] == 0:
                    payment_status_text = "待支付"
                elif order_data['payment_status'] == 1:
                    payment_status_text = "已支付"
                elif order_data['payment_status'] == 2:
                    payment_status_text = "支付失败"
                elif order_data['payment_status'] == 3:
                    payment_status_text = "已退款"
                elif order_data['payment_status'] == 4:
                    payment_status_text = "已关闭"
                else:
                    payment_status_text = "未知状态"
            else:
                payment_status_text = "未找到订单"
                
        except Exception as db_e:
            logger.error(f"查询订单信息出错: {str(db_e)}")
            logger.error(traceback.format_exc())
            user_data = None
            payment_status_text = "查询失败"
        finally:
            if cursor:
                try:
                    cursor.close()
                except:
                    pass
            if conn:
                try:
                    conn.close()
                except:
                    pass
        
        # 构建HTML响应
        html_content = f"""
        <!DOCTYPE html>
        <html>
        <head>
            <meta charset="utf-8">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <title>支付结果</title>
            <style>
                body {{ font-family: Arial, sans-serif; margin: 0; padding: 20px; text-align: center; background-color: #f7f7f7; }}
                .result-container {{ max-width: 500px; margin: 0 auto; padding: 20px; border: 1px solid #ddd; border-radius: 8px; background-color: #fff; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }}
                .success {{ color: #2ecc71; }}
                .warning {{ color: #f39c12; }}
                .error {{ color: #e74c3c; }}
                .info {{ margin: 15px 0; }}
                h2 {{ color: #333; }}
                table {{ width: 100%; border-collapse: collapse; margin: 15px 0; }}
                table, th, td {{ border: 1px solid #eee; }}
                th, td {{ padding: 10px; text-align: left; }}
                th {{ background-color: #f8f9fa; }}
                .btn {{ display: inline-block; padding: 10px 15px; background-color: #3498db; color: white; text-decoration: none; border-radius: 4px; margin-top: 10px; }}
                .note {{ font-size: 12px; color: #777; margin-top: 20px; }}
            </style>
        </head>
            <body>
            <div class="result-container">
                <h2>支付结果</h2>
                
                <div class="info">
                    <table>
                        <tr>
                            <th>订单编号</th>
                            <td>{out_trade_no or "未知"}</td>
                        </tr>
                        <tr>
                            <th>商品信息</th>
                            <td>{order_data['order_title'] if order_data else "未知商品"}</td>
                        </tr>
                        <tr>
                            <th>支付金额</th>
                            <td>{total_amount or (order_data['amount'] if order_data else "未知")} 元</td>
                        </tr>
                        <tr>
                            <th>购买积分</th>
                            <td>{order_data['points'] if order_data else "未知"} 积分</td>
                        </tr>
                        <tr>
                            <th>支付宝交易号</th>
                            <td>{trade_no or "未知"}</td>
                        </tr>
                        <tr>
                            <th>支付状态</th>
                            <td class="{
                                'success' if payment_status_text == '已支付' else 
                                'warning' if payment_status_text == '待支付' else 
                                'error'
                            }">{payment_status_text}</td>
                        </tr>
                        <tr>
                            <th>支付时间</th>
                            <td>{order_data['payment_time'] if order_data and order_data['payment_time'] else "未知"}</td>
                        </tr>
                        <tr>
                            <th>用户信息</th>
                            <td>{user_data['username'] if user_data else "未知用户"}</td>
                        </tr>
                        <tr>
                            <th>当前积分</th>
                            <td>{user_data['userPoints'] if user_data else "未知"}</td>
                        </tr>
                        <tr>
                            <th>签名验证</th>
                            <td class="{'success' if verify_result else 'error'}">{('成功' if verify_result else '失败')}</td>
                        </tr>
                    </table>
                </div>
                
                <p class="note">注意：此页面仅作为支付结果展示，实际订单处理以系统异步通知为准。</p>
                <p><a href="javascript:window.close();" class="btn">关闭窗口</a></p>
            </div>
            <script>
                // 3秒后自动关闭页面（如果是在APP内的webview中打开的）
                setTimeout(function() {{
                    // 检测是否在APP内的WebView中
                    if (window.navigator.userAgent.indexOf('Mobile') !== -1) {{
                        window.close();
                    }}
                }}, 3000);
            </script>
            </body>
        </html>
        """
        
        return HTMLResponse(content=html_content)
        
    except Exception as e:
        logger.error(f"处理支付宝同步跳转时出错: {str(e)}")
        logger.error(traceback.format_exc())
        return HTMLResponse(f"<h1>处理支付宝返回时出错</h1><p>{str(e)}</p>")


if __name__ == "__main__":
    config = uvicorn.Config(app, host="0.0.0.0", port=2233, loop="asyncio")
    server = uvicorn.Server(config)
    loop = asyncio.get_event_loop()
    loop.run_until_complete(server.serve())